This shows you the differences between two versions of the page.
|
admin:optimization:plans:optimization [2012/02/03 03:17] doc created |
admin:optimization:plans:optimization [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Administration: Optimizing Queries ====== | ||
| - | |||
| - | ===== Query Plans ===== | ||
| - | |||
| - | |||
| - | [[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]]** | ||
| - | |||
| - | ---- | ||
| - | \\ | ||
| - | |||
| - | ==== Optimizing Queries ==== | ||
| - | |||
| - | |||
| - | <code> | ||
| - | ----------------------------------- 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 Jan 23 2012 17:27:25) | ||
| - | Warnings: UNOPTIMIZED_CRITERIA, UNOPTIMIZED_AGGREGATION, UNOPTIMIZED_SORT, | ||
| - | SEQUENTIAL_SCAN, SEQUENTIAL_TABLE_JOIN | ||
| - | Notes: Optimized aggregations are not possible because no ASK index | ||
| - | contains all columns and all links to dimension tables | ||
| - | HDC optimization not used because table INDIVIDUALS has Zero | ||
| - | cardinality. | ||
| - | SortMerge optimization not used because table HOUSEHOLDS has Zero | ||
| - | cardinality. | ||
| - | Sequential Table Join on INDIVIDUALS with H.HOUSEHOLD = | ||
| - | I.HOUSEHOLD | ||
| - | Filter on column STATE will not be optimized because there is not | ||
| - | an MDK index installed on the column. | ||
| - | Filter on column CITY will not be optimized because there is not | ||
| - | an MDK index installed on the column. | ||
| - | Filter on column BIRTHDATE will not be optimized because there is | ||
| - | not an MDK index installed on the column. | ||
| - | ----------------------------------- DETAILS ----------------------------------- | ||
| - | Retrieve HOUSEHOLDS H sequentially; | ||
| - | Retrieve INDIVIDUALS I sequentially; | ||
| - | Filter H.HOUSEHOLD = I.HOUSEHOLD; | ||
| - | Filter I.BIRTHDATE > 'January 1, 1980'; | ||
| - | Filter H.STATE = 'CO'; | ||
| - | Filter H.CITY = 'DENVER'; | ||
| - | Filter FILTER 0 AND FILTER 1; | ||
| - | Filter H.STATE = 'AZ'; | ||
| - | Filter H.CITY = 'PHOENIX'; | ||
| - | Filter FILTER 3 AND FILTER 4; | ||
| - | Filter FILTER 2 OR FILTER 5; | ||
| - | Pass to queue {1} [I.GENDER]; | ||
| - | Sort {1} for GROUP BY [I.GENDER]; | ||
| - | Retrieve {1} sequentially; | ||
| - | Return I.GENDER, COUNT('*'); | ||
| - | ------------------------------------------------------------------------------- | ||
| - | > | ||
| - | </code> | ||
| - | |||
| - | A plan consists of two sections: the Summary and the Details. The Summary section shows the SQL statement, the version of Omnidex, any optimization settings or warnings that affect the query, and even occasional suggestions for optimizing the query. The Details section shows the steps that will be executed to satisfy the query. Steps are executed in the order displayed, and are indented as needed to show loops of instructions. | ||
| - | |||
| - | In the simple example above, three columns are retrieved from the INDIVIDUALS table. Since no criteria was provided in a WHERE clause, the details of the plan show that the table is retrieved sequentially and the columns are returned. A simple warning indicates that a sequential scan is taking place, which in this case cannot be avoided. | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | ---- | ||
| - | \\ | ||
| - | **[[admin:optimization:reference|Next]]** | ||
| - | |||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | See also: | ||
| - | {{page>:admin:indexing:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||