This shows you the differences between two versions of the page.
admin:optimization:plans:example [2012/02/02 17:24] doc |
admin:optimization:plans:example [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ~~NOTOC~~ | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | |||
- | ====== Administration ====== | ||
- | |||
- | ===== Optimizing Queries ===== | ||
- | |||
- | |||
- | **[[admin:optimization:plans:home|Overview]]** | | ||
- | [[admin:optimization:plans:reference|Anatomy of a Query Plan]] | | ||
- | [[admin:optimization:plans:example|Reading a Query Plan]] | | ||
- | [[admin:optimization:plans:configuration|Configuring Query Plans]] | | ||
- | [[admin:optimization:plans:optimization|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 how Omnidex optimizes this query. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td valign="top"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where     ((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER  | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | === 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. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   ----------------------------------- SUMMARY -----------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td valign="top" width="50%"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    <font color=red>I.GENDER, count(*) </font><br /> | ||
- |   <font color=red> from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD </font><br /> | ||
- |   where   ((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   <font color=red>group by  I.GENDER </font><br /> | ||
- | </br> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <--<br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | Since this query aggregates INDIVIDUALS, <br /> | ||
- | Omnidex will process HOUSEHOLDS first and <br /> | ||
- | then end up in INDIVIDUALS. <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | |||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) </br> | ||
- |   ----------------------------------- DETAILS -----------------------------------</br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO'; </br> | ||
- |   Create index segment O1 on 1; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)'; </br> | ||
- |   Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD; </br> | ||
- |   Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'; </br> | ||
- |   Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*); </br> | ||
- |   Return I.GENDER, COUNT('*'); </br> | ||
- |   -------------------------------------------------------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | |||
- | === 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 the rows that meet this criteria, identified by a temporary file containing index pointers. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   ----------------------------------- SUMMARY -----------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td valign="top" width="50%"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where   <font color=red>((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix'))</font> and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER <br /> | ||
- | </br> | ||
- | |||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <--<br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | |||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | Omnidex will use the STATE and CITY indexes<br /> | ||
- | to process HOUSEHOLDS's criteria, honoring <br /> | ||
- | Boolean operators and parentheses. <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | |||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) </br> | ||
- |   ----------------------------------- DETAILS -----------------------------------</br> | ||
- |  <font color=red> Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO'; </br> | ||
- |   Create index segment O1 on 1; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)'; </font> </br> | ||
- |   Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD; </br> | ||
- |   Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'; </br> | ||
- |   Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*); </br> | ||
- |   Return I.GENDER, COUNT('*'); </br> | ||
- |   -------------------------------------------------------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | |||
- | |||
- | === 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. Once this is done, Omnidex will have isolated rows in the INDIVIDUALS table that meet the criteria from the HOUSEHOLDS table. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   ----------------------------------- SUMMARY -----------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td valign="top" width="50%"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where   ((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER <br /> | ||
- | </br> | ||
- | |||
- |   Select   I.GENDER, count(*) <br /> | ||
- |   <font color=red>from   HOUSEHOLDS H <br /> | ||
- |   join   INDIVIDUALS I <br /> | ||
- |   on H.HOUSEHOLD = I.HOUSEHOLD </font> <br /> | ||
- |   where    ((H.STATE = 'CO' and <br /> | ||
- |    H.CITY = 'Denver') or <br /> | ||
- |   (H.STATE = 'AZ' and <br /> | ||
- |    H.CITY = 'Phoenix')) and <br /> | ||
- |    I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by   I.GENDER <br /> | ||
- | </br> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <--<br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | |||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | Omnidex will apply primary keys from <br /> | ||
- | HOUSEHOLDS against the HOUSEHOLD index <br /> | ||
- | in INDIVIDUALS to process this join. <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | |||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) </br> | ||
- |   ----------------------------------- DETAILS -----------------------------------</br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO'; </br> | ||
- |   Create index segment O1 on 1; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)'; </br> | ||
- |  <font color=red> Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD; </font> </br> | ||
- |   Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'; </br> | ||
- |   Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*); </br> | ||
- |   Return I.GENDER, COUNT('*'); </br> | ||
- |   -------------------------------------------------------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | |||
- | |||
- | === 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 already identified by processing in the HOUSEHOLDS table. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   ----------------------------------- SUMMARY -----------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td valign="top" width="50%"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where   ((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER <br /> | ||
- | </br> | ||
- | |||
- |   Select   I.GENDER, count(*) <br /> | ||
- |   from   HOUSEHOLDS H <br /> | ||
- |   join   INDIVIDUALS I <br /> | ||
- |   on H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where    ((H.STATE = 'CO' and <br /> | ||
- |    H.CITY = 'Denver') or <br /> | ||
- |   (H.STATE = 'AZ' and <br /> | ||
- |    H.CITY = 'Phoenix')) and <br /> | ||
- |    <font color=red>I.BIRTHDATE > 'January 1, 1980'</font> <br /> | ||
- |   group by   I.GENDER <br /> | ||
- | </br> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <--<br /> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | Omnidex will use the BIRTHDATE index to <br /> | ||
- | process INDIVIDUALS's criteria. | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) </br> | ||
- |   ----------------------------------- DETAILS -----------------------------------</br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO'; </br> | ||
- |   Create index segment O1 on 1; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)'; </br> | ||
- |   Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD; </br> | ||
- |  <font color=red> Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'; </font></br> | ||
- |   Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*); </br> | ||
- |   Return I.GENDER, COUNT('*'); </br> | ||
- |   -------------------------------------------------------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | === Step 5: Aggregate counts in the INDIVIDUALS table=== | ||
- | |||
- | Omnidex will aggregate counts in the INDIVIDUALS table by scanning its indexes and filering the results using the index pointers set aside from the previous steps. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   ----------------------------------- SUMMARY -----------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td valign="top" width="50%"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where   ((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER <br /> | ||
- | </br> | ||
- | |||
- |   Select   <font color=red>I.GENDER, count(*) </font><br /> | ||
- |   from   HOUSEHOLDS H <br /> | ||
- |   join   INDIVIDUALS I <br /> | ||
- |   on H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where    ((H.STATE = 'CO' and <br /> | ||
- |    H.CITY = 'Denver') or <br /> | ||
- |   (H.STATE = 'AZ' and <br /> | ||
- |    H.CITY = 'Phoenix')) and <br /> | ||
- |    I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   <font color=red>group by   I.GENDER </font> | ||
- | </br> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <--<br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <-- </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | Omnidex will aggregate counts using the <br /> | ||
- | GENDER index, based on qualified rows. <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) </br> | ||
- |   ----------------------------------- DETAILS -----------------------------------</br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO'; </br> | ||
- |   Create index segment O1 on 1; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)'; </br> | ||
- |   Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD; </br> | ||
- |   Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'; </br> | ||
- |  <font color=red> Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*); </font></br> | ||
- |   Return I.GENDER, COUNT('*'); </br> | ||
- |   -------------------------------------------------------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | === 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 requested. | ||
- | |||
- | <html> | ||
- | <table width="100%"> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   ----------------------------------- SUMMARY -----------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td valign="top" width="50%"> | ||
- | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | ||
- |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where   ((H.STATE = 'CO' and <br /> | ||
- |   H.CITY = 'Denver') or <br /> | ||
- |    (H.STATE = 'AZ' and <br /> | ||
- |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER <br /> | ||
- | </br> | ||
- | |||
- |   Select   I.GENDER, count(*) <br /> | ||
- |   from   HOUSEHOLDS H <br /> | ||
- |   join   INDIVIDUALS I <br /> | ||
- |   on H.HOUSEHOLD = I.HOUSEHOLD <br /> | ||
- |   where    ((H.STATE = 'CO' and <br /> | ||
- |    H.CITY = 'Denver') or <br /> | ||
- |   (H.STATE = 'AZ' and <br /> | ||
- |    H.CITY = 'Phoenix')) and <br /> | ||
- |    I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by   <font color=red>I.GENDER</font> <br /> | ||
- | </br> | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <-- | ||
- | </font> | ||
- | </td> | ||
- | <td valign="top"> | ||
- | <font color=red> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | <br /> | ||
- | Omnidex will return the result set. | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | <tr> | ||
- | <td colspan="3"> | ||
- | <font face="'Courier New'"> | ||
- |   Version: 5.2.01 (Compiled Jan 23 2012 17:27:25) </br> | ||
- |   ----------------------------------- DETAILS -----------------------------------</br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Denver'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'CO'; </br> | ||
- |   Create index segment O1 on 1; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where CITY = 'Phoenix'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where and STATE = 'AZ'; </br> | ||
- |   Qualify (HOUSEHOLDS)HOUSEHOLDS where or $ODXID = 'segment(O1)'; </br> | ||
- |   Join HOUSEHOLDS using HOUSEHOLD to (INDIVIDUALS)INDIVIDUALS using HOUSEHOLD; </br> | ||
- |   Qualify (INDIVIDUALS)INDIVIDUALS where and BIRTHDATE > '"January 1, 1980"'; </br> | ||
- |   Aggregate INDIVIDUALS using GENDER for GROUP(GENDER), COUNT(*); </br> | ||
- |  <font color=red> Return I.GENDER, COUNT('*'); </font></br> | ||
- |   -------------------------------------------------------------------------------</br> | ||
- | </font> | ||
- | </td> | ||
- | </tr> | ||
- | </table> | ||
- | </html> | ||
- | |||
- | |||
- | ===== ===== | ||
- | ---- | ||
- | \\ | ||
- | **[[admin:optimization:plans:reference|Prev]]** | | ||
- | **[[admin:optimization:plans:configuration|Next]]** | ||
- | |||
- | |||
- | ====== Additional Resources ====== | ||
- | See also: | ||
- | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} |