This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
admin:optimization:plans:example [2012/02/02 17:24] doc |
admin:optimization:plans:example [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 3: | Line 3: | ||
{{page>:top_add&nofooter&noeditbtn}} | {{page>:top_add&nofooter&noeditbtn}} | ||
- | ====== Administration ====== | + | ====== Administration: Optimizing Queries ====== |
- | ===== Optimizing Queries ===== | + | ===== Query Plans ===== |
- | **[[admin:optimization:plans:home|Overview]]** | | + | [[admin:optimization:plans:home|Overview]] | |
[[admin:optimization:plans:reference|Anatomy of a Query Plan]] | | [[admin:optimization:plans:reference|Anatomy of a Query Plan]] | | ||
- | [[admin:optimization:plans:example|Reading a Query Plan]] | | + | **[[admin:optimization:plans:example|Reading a Query Plan]]** | |
[[admin:optimization:plans:configuration|Configuring Query Plans]] | | [[admin:optimization:plans:configuration|Configuring Query Plans]] | | ||
[[admin:optimization:plans:optimization|Optimizing Queries]] | [[admin:optimization:plans:optimization|Optimizing Queries]] | ||
Line 19: | Line 19: | ||
==== Reading a Query Plan ==== | ==== 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. | + | 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. |
<html> | <html> | ||
Line 124: | Line 124: | ||
=== Step 2: Process the HOUSEHOLDS table's criteria === | === 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. | + | 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. |
<html> | <html> | ||
Line 207: | Line 207: | ||
=== Step 3: Join from HOUSEHOLDS to INDIVIDUALS === | === 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. | + | 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. |
<html> | <html> | ||
Line 222: | Line 222: | ||
<font face="'Courier New'"> | <font face="'Courier New'"> | ||
  Select    I.GENDER, count(*) <br /> |   Select    I.GENDER, count(*) <br /> | ||
- |   from    HOUSEHOLDS H <br /> | + |   <font color=red>from    HOUSEHOLDS H <br /> |
  join    INDIVIDUALS I <br /> |   join    INDIVIDUALS I <br /> | ||
- |   on    H.HOUSEHOLD = I.HOUSEHOLD <br /> | + |   on    H.HOUSEHOLD = I.HOUSEHOLD </font> <br /> |
  where   ((H.STATE = 'CO' and <br /> |   where   ((H.STATE = 'CO' and <br /> | ||
  H.CITY = 'Denver') or <br /> |   H.CITY = 'Denver') or <br /> | ||
Line 233: | Line 233: | ||
</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> | </font> | ||
</td> | </td> | ||
Line 301: | Line 290: | ||
=== Step 4: Process the INDIVIDUALS table's criteria === | === 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. | + | 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. |
<html> | <html> | ||
Line 323: | Line 312: | ||
   (H.STATE = 'AZ' and <br /> |    (H.STATE = 'AZ' and <br /> | ||
  H.CITY = 'Phoenix')) and <br /> |   H.CITY = 'Phoenix')) and <br /> | ||
- |   I.BIRTHDATE > 'January 1, 1980' <br /> | + |   <font color=red>I.BIRTHDATE > 'January 1, 1980' </font><br /> |
  group by  I.GENDER <br /> |   group by  I.GENDER <br /> | ||
</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> | </font> | ||
</td> | </td> | ||
Line 392: | Line 370: | ||
=== Step 5: Aggregate counts in the INDIVIDUALS table=== | === 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. | + | 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. |
<html> | <html> | ||
Line 406: | Line 384: | ||
<td valign="top" width="50%"> | <td valign="top" width="50%"> | ||
<font face="'Courier New'"> | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | + |   Select    <font color=red>I.GENDER, count(*) </font><br /> |
  from    HOUSEHOLDS H <br /> |   from    HOUSEHOLDS H <br /> | ||
  join    INDIVIDUALS I <br /> |   join    INDIVIDUALS I <br /> | ||
Line 415: | Line 393: | ||
  H.CITY = 'Phoenix')) and <br /> |   H.CITY = 'Phoenix')) and <br /> | ||
  I.BIRTHDATE > 'January 1, 1980' <br /> |   I.BIRTHDATE > 'January 1, 1980' <br /> | ||
- |   group by  I.GENDER <br /> | + |   <font color=red>group by  I.GENDER </font><br /> |
</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> | </font> | ||
</td> | </td> | ||
Line 483: | Line 449: | ||
=== Step 6: Return the final result set === | === 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. | + | 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. |
<html> | <html> | ||
Line 497: | Line 463: | ||
<td valign="top" width="50%"> | <td valign="top" width="50%"> | ||
<font face="'Courier New'"> | <font face="'Courier New'"> | ||
- |   Select    I.GENDER, count(*) <br /> | + |   Select    <font color=red>I.GENDER, count(*) </font><br /> |
  from    HOUSEHOLDS H <br /> |   from    HOUSEHOLDS H <br /> | ||
  join    INDIVIDUALS I <br /> |   join    INDIVIDUALS I <br /> | ||
Line 508: | Line 474: | ||
  group by  I.GENDER <br /> |   group by  I.GENDER <br /> | ||
</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> | </font> | ||
</td> | </td> | ||
<td valign="top"> | <td valign="top"> | ||
<font color=red> | <font color=red> | ||
+ | <--<br /> | ||
<br /> | <br /> | ||
<br /> | <br /> | ||
Line 532: | Line 487: | ||
<br /> | <br /> | ||
<br /> | <br /> | ||
- | <br /> | ||
- | <-- | ||
</font> | </font> | ||
</td> | </td> | ||
<td valign="top"> | <td valign="top"> | ||
<font color=red> | <font color=red> | ||
+ | Omnidex will return the result set. <br /> | ||
<br /> | <br /> | ||
<br /> | <br /> | ||
Line 546: | Line 500: | ||
<br /> | <br /> | ||
<br /> | <br /> | ||
- | <br /> | ||
- | Omnidex will return the result set. | ||
</font> | </font> | ||
</td> | </td> | ||
Line 583: | Line 535: | ||
====== Additional Resources ====== | ====== Additional Resources ====== | ||
See also: | See also: | ||
- | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | + | {{page>:admin:optimization:see_also&nofooter&noeditbtn}} |
{{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} |