Show pagesource
Old revisions

Administration: Optimizing Queries

Query Plans

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


Reading a Query Plan

The following is a rather straightforward query that joins two tables together — Individuals and their respective Households — and aggregates counts of all people born since 1980 that are either in Denver, CO or in Phoenix, AZ. The result is a display of counts by Gender. Let's take a look at the query plan to understand how Omnidex optimizes this query.

  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 

Step 1: Determine the processing order

Omnidex must first determine the best processing order for the query. This is determined based on analyzing the table relationships and the select items. Generally speaking, Omnidex needs to end up in the table where the select items or aggregations reside, and must process the other tables first. In this example, Omnidex will recognize that the HOUSEHOLDS table is the parent table, and the query will aggregate data in the INDIVIDUALS table. It will process the HOUSEHOLDS criteria first.

  ----------------------------------- 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 

 		<--








 		Since this query aggregates INDIVIDUALS,
Omnidex will process HOUSEHOLDS first and
then end up in INDIVIDUALS.






  Version: 5.2.01 (Compiled Jan 23 2012 17:27:25)
  ----------------------------------- DETAILS -----------------------------------
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
  Create index segment O1 on 1;
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
  Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
  Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
  Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
  Return I.GENDER, COUNT('*');
  -------------------------------------------------------------------------------

Step 2: Process the HOUSEHOLDS table's criteria

Omnidex will perform index qualifications to process the criteria in the HOUSEHOLDS table, paying attention to Boolean operators and parentheses to insure the correct result. Once this is done, Omnidex will have isolated index pointers for the rows that meet this criteria.

  ----------------------------------- 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 



<--



Omnidex will use the STATE and CITY indexes
to process HOUSEHOLDS's criteria, honoring
Boolean operators and parentheses.


  Version: 5.2.01 (Compiled Jan 23 2012 17:27:25)
  ----------------------------------- DETAILS -----------------------------------
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
  Create index segment O1 on 1;
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
  Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
  Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
  Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
  Return I.GENDER, COUNT('*');
  -------------------------------------------------------------------------------

Step 3: Join from HOUSEHOLDS to INDIVIDUALS

Omnidex will join from the HOUSEHOLDS table to the INDIVIDUALS table. Omnidex has several techniques for optimizing table joins. In this case, it will use the primary key values in HOUSEHOLDS as criteria against thed HOUSEHOLD index in the INDIVIDUALS table. Omnidex will then have isolated index pointers for rows in the INDIVIDUALS table that link to the previously qualified HOUSEHOLDS rows.

  ----------------------------------- 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 
<--
Omnidex will apply primary keys from
HOUSEHOLDS against the HOUSEHOLD index
in INDIVIDUALS to process this join.





  Version: 5.2.01 (Compiled Jan 23 2012 17:27:25)
  ----------------------------------- DETAILS -----------------------------------
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
  Create index segment O1 on 1;
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
  Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
  Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
  Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
  Return I.GENDER, COUNT('*');
  -------------------------------------------------------------------------------

Step 4: Process the INDIVIDUALS table's criteria

Omnidex will perform index qualifications to process the criteria in the INDIVIDUALS table. This will further refine the index pointers so that they reflect criteria from both tables, as well as the table join itself.

  ----------------------------------- 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 







<--







Omnidex will use the BIRTHDATE index to
process INDIVIDUALS's criteria.
  Version: 5.2.01 (Compiled Jan 23 2012 17:27:25)
  ----------------------------------- DETAILS -----------------------------------
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
  Create index segment O1 on 1;
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
  Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
  Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
  Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
  Return I.GENDER, COUNT('*');
  -------------------------------------------------------------------------------

Step 5: Aggregate counts in the INDIVIDUALS table

Omnidex will aggregate counts in the INDIVIDUALS table using indexes on the GROUP BY columns, while also intersecting with the index pointers that were isolated while processing criteria and table joins.

  ----------------------------------- 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 

 		<--








<-- 		Omnidex will aggregate counts using the
GENDER index, based on qualified rows.








  Version: 5.2.01 (Compiled Jan 23 2012 17:27:25)
  ----------------------------------- DETAILS -----------------------------------
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
  Create index segment O1 on 1;
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
  Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
  Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
  Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
  Return I.GENDER, COUNT('*');
  -------------------------------------------------------------------------------

Step 6: Return the final result set

Omnidex will finally return the result set of the query. When possible, Omnidex will return the first rows as soon as they are available, even if the later rows are still being processed. This speeds query execution, and also helps when only the first screenfuls of data are going to be viewed.

  ----------------------------------- 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 

 		<--








 		Omnidex will return the result set.








  Version: 5.2.01 (Compiled Jan 23 2012 17:27:25)
  ----------------------------------- DETAILS -----------------------------------
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO';
  Create index segment O1 on 1;
  Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ';
  Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)';
  Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD;
  Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"';
  Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*);
  Return I.GENDER, COUNT('*');
  -------------------------------------------------------------------------------


Prev | Next

Additional Resources

See also:

 
Back to top
admin/optimization/plans/example.txt · Last modified: 2016/06/28 22:38 (external edit)
Sitemap Recent Changes