This shows you the differences between two versions of the page.
admin:features:rollups:optimization [2012/02/22 22:31] doc |
admin:features:rollups:optimization [2016/06/28 22:38] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | ~~NOTOC~~ | ||
- | {{page>:top_add&nofooter&noeditbtn}} | ||
- | |||
- | ====== Administration: Omnidex Features ====== | ||
- | |||
- | ===== Rollup Tables ===== | ||
- | |||
- | [[admin:features:rollups:home|Overview]] | [[admin:features:rollups:design|Design]] | [[admin:features:rollups:create|Creation]] | [[admin:features:rollups:indexing|Indexing]] | **[[admin:features:rollups:optimization|Optimization]]** | ||
- | |||
- | ---- | ||
- | |||
- | ==== Optimizing Queries using Rollup Tables ==== | ||
- | |||
- | Omnidex will directly use Rollup Tables to optimize queries. This means that the queries themselves do not need to be changed. When rollup tables are declared, their relationship to the underlying data is understood. This allows Omnidex to look at queries against the underlying data and seamlessly query the rollup table. This design allows the Omnidex Administrator to create and deploy rollup tables based on the changing needs of the queries, much the same way that indexes are added as needed. Developers simply write applications against the underlying database, yet automatically enjoy the improved performance from the rollup table. | ||
- | |||
- | It is possible to directly query the rollup table, but it is generally not recommended. The rollup table is a legitimate table in the environment, but once applications are written that rely on the rollup table, it becomes more difficult to change the rollups to meet the changing needs of the queries. | ||
- | |||
- | ==== Examples === | ||
- | |||
- | The following are examples of queries that can be redirected to the rollup table using the example shown earlier. The query plan is shown on each of these to illustrate how the redirection to the rollup table occurs. | ||
- | |||
- | |||
- | === Simple count === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select count(*) | ||
- | from INDIVIDUALS; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Aggregate INDIVIDUALS_BY_DEMO using NUM_INDIVIDUALS for SUM(NUM_INDIVIDUALS); | ||
- | Return CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Simple count with criteria === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select count(*) | ||
- | from INDIVIDUALS | ||
- | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000'; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan | ||
- | 1, 1980"' and '"Dec 31, 2000"'; | ||
- | Aggregate INDIVIDUALS_BY_DEMO using NUM_INDIVIDUALS for SUM(NUM_INDIVIDUALS); | ||
- | Return CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Simple count distinct === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select count(distinct GENDER) | ||
- | from INDIVIDUALS | ||
- | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000'; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan | ||
- | 1, 1980"' and '"Dec 31, 2000"'; | ||
- | Aggregate INDIVIDUALS_BY_DEMO using GENDER for COUNT(DISTINCT GENDER) on 1; | ||
- | Return COUNT(DISTINCT INDIVIDUALS_BY_DEMO.GENDER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Simple count distinct with criteria === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select GENDER, | ||
- | count(*) | ||
- | from INDIVIDUALS | ||
- | group by GENDER; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), | ||
- | SUM(NUM_INDIVIDUALS); | ||
- | Return INDIVIDUALS_BY_DEMO.GENDER, | ||
- | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Simple grouped count === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select GENDER, | ||
- | count(*) | ||
- | from INDIVIDUALS | ||
- | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000' | ||
- | group by GENDER; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan | ||
- | 1, 1980"' and '"Dec 31, 2000"'; | ||
- | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), | ||
- | SUM(NUM_INDIVIDUALS); | ||
- | Return INDIVIDUALS_BY_DEMO.GENDER, | ||
- | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Simple grouped count with criteria === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select GENDERS.DESCRIPTION, | ||
- | count(*) | ||
- | from INDIVIDUALS | ||
- | join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER | ||
- | group by GENDERS.DESCRIPTION; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; | ||
- | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), | ||
- | SUM(NUM_INDIVIDUALS) on 1; | ||
- | Retrieve {1} using PK_GENDER = INDIVIDUALS_BY_DEMO.GENDER; | ||
- | Pass to queue {2} [GENDERS.DESCRIPTION, | ||
- | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER)]; | ||
- | Sort {2} for GROUP BY [GENDERS.DESCRIPTION]; | ||
- | Retrieve {2} sequentially; | ||
- | Return GENDERS.DESCRIPTION, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS | ||
- | UNSIGNED INTEGER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Count grouped by joined table === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select GENDERS.DESCRIPTION, | ||
- | count(*) | ||
- | from INDIVIDUALS | ||
- | join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER | ||
- | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000' | ||
- | group by GENDERS.DESCRIPTION; | ||
- | |||
- | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) | ||
- | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan | ||
- | 1, 1980"' and '"Dec 31, 2000"' on 1; | ||
- | Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; | ||
- | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), | ||
- | SUM(NUM_INDIVIDUALS) on 1; | ||
- | Retrieve {1} using PK_GENDER = INDIVIDUALS_BY_DEMO.GENDER; | ||
- | Pass to queue {2} [GENDERS.DESCRIPTION, | ||
- | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER)]; | ||
- | Sort {2} for GROUP BY [GENDERS.DESCRIPTION]; | ||
- | Retrieve {2} sequentially; | ||
- | Return GENDERS.DESCRIPTION, CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS | ||
- | UNSIGNED INTEGER); | ||
- | ------------------------------------------------------------------------------- | ||
- | </code> | ||
- | |||
- | === Count grouped by joined table with criteria === | ||
- | |||
- | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select STATES.DESCRIPTION, \ | ||
- | count(*) \ | ||
- | from LIST \ | ||
- | join STATES on LIST.STATE = STATES.STATE \ | ||
- | where CITY = 'Boulder' \ | ||
- | group by STATES.DESCRIPTION | ||
- | |||
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | ||
- | Optimization: MDKQUAL, AGGREGATION, STARSCHEMA, ROLLUP | ||
- | Notes: Table LIST translated to Rollup Table LIST_GEO | ||
- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET | ||
- | Build cache {C1} as (SELECT DESCRIPTION, STATE FROM STATES) | ||
- | Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1 | ||
- | Retrieve {C1} using PK_STATE = LIST_GEO.STATE | ||
- | Pass to queue {1} [STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)] | ||
- | Sort {1} for GROUP BY [STATES.DESCRIPTION ASC] | ||
- | Retrieve {1} sequentially | ||
- | Return STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | ||
- | ------------------------------------------------------------------------------- | ||
- | |||
- | </code> | ||
- | |||
- | ==== ==== | ||
- | **[[admin:features:rollups:indexing|Prev]]** | ||
- | |||
- | ====== Additional Resources ====== | ||
- | |||
- | {{page>:admin:features:see_also&nofooter&noeditbtn}} | ||
- | |||
- | {{page>:bottom_add&nofooter&noeditbtn}} |