Overview | Anatomy of a Query Plan | Reading a Query Plan | Configuring Query Plans | Optimizing Queries

Omnidex Query Plans have some configuration options that will increase the amount of information available. These are available using the SET EXPLAIN command in OdxSQL. The syntax of the SET EXPLAIN command is:

SET EXPLAIN [COUNTS] [TEXT]

The COUNTS option will display row counts and timings in the query plan after certain steps. This option only affects query plans that are run after the query has completed. First, issue the query in OdxSQL and let it run to completion, and then issue the “EXPLAIN” command by itself.

Counts and timings can be quite useful in determining where query performance is most affected. The follow query plan shows the counts and timings for a well-optimized query:

> set explain counts
> explain
----------------------------------- SUMMARY -----------------------------------
Select        I.GENDER,
              count(*)
  from        HOUSEHOLDS H
  join        INDIVIDUALS I on H.HOUSEHOLD = I.HOUSEHOLD
  where       ((H.STATE = 'CO' and
                H.CITY = 'DENVER') or
               (H.STATE = 'AZ' and
                H.CITY = 'PHOENIX')) and
              I.BIRTHDATE > 'January 1, 1980'
  group by    I.GENDER;

Version:      5.2.01  (Compiled Feb  2 2012 21:29:57)
----------------------------------- DETAILS -----------------------------------
Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'DENVER';
   (15 HOUSEHOLDS, 15 Pre-intersect   0.000 CPU    0.015 Elapsed)
Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
   (15 HOUSEHOLDS, 67 Pre-intersect   0.000 CPU    0.000 Elapsed)
Create index segment O1;
   (15 rows exported   0.000 CPU    0.047 Elapsed)
Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'PHOENIX';
   (11 HOUSEHOLDS, 11 Pre-intersect   0.000 CPU    0.000 Elapsed)
Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
   (11 HOUSEHOLDS, 61 Pre-intersect   0.000 CPU    0.000 Elapsed)
Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
   (26 HOUSEHOLDS, 15 Pre-intersect   0.000 CPU    0.000 Elapsed)
Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
   (64 INDIVIDUALS, 64 Pre-intersect   0.016 CPU    0.016 Elapsed)
Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
   (24 INDIVIDUALS, 1,882 Pre-intersect   0.000 CPU    0.000 Elapsed)
Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
   (2 rows   0.000 CPU    0.000 Elapsed)
Return I.GENDER, COUNT('*');
-------------------------------------------------------------------------------
> quit

The TEXT option displays more information about the expansion of the query due to the use of PowerSearch. PowerSearch allows a query to locate rows even if they do not exactly match the criteria using techniques like synonyms, misspellings, transpositions, word forms, etc. The TEXT option will show the exact search terms after the query was expanded.

The follow query plan shows the expanded search terms for a PowerSearch query. In this example, PowerSearch is being used to find all the synonyms and phonetic equivalents of William, and the spelling variations of an email address containing Vasquez. The query plan shows several synonyms for William and two email addresses that are similar to Vasquez.

> set explain text
> explain
----------------------------------- SUMMARY -----------------------------------
Select        NAME,
              EMAIL
  from        INDIVIDUALS
  where       NAME = 'William' and
              EMAIL = 'vasquez'
  with        OPTIMIZATION=no_cachequal POWERSEARCH;

Version:      5.2.01  (Compiled Feb  2 2012 21:29:57)
----------------------------------- DETAILS -----------------------------------
Qualify (INDIVIDUALS)INDIVIDUALS where NAME = 'synonyms(William,
   "all_given_names") or phonetic(William)' on 1 with NOAUTORESET;
   ----------------------------- Text translation -----------------------------
   in (William, Bill, Billy, Will, Williams, Willie, Willis, Wilson) or
   phonetic(William)
   ----------------------------------------------------------------------------
Qualify (INDIVIDUALS)INDIVIDUALS where and EMAIL = 'misspellings(vasquez,,
   "MIN_SCORE=70")' on 1 with NOAUTORESET;
   ----------------------------- Text translation -----------------------------
   and
   in (kvasquez80, bvasquez912, vasquez)
   ----------------------------------------------------------------------------
Fetchkeys $ROWID 1,000 at a time on 1;
 Retrieve INDIVIDUALS using $ROWID = $ODXID;
 Return INDIVIDUALS.NAME, INDIVIDUALS.EMAIL;
-------------------------------------------------------------------------------

Next

See also: