This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
admin:features:rollups:optimization [2011/03/25 15:44] deb |
admin:features:rollups:optimization [2016/06/28 22:38] (current) |
||
---|---|---|---|
Line 7: | Line 7: | ||
===== Rollup Tables ===== | ===== 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]]** | + | [[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]]** |
---- | ---- | ||
Line 19: | Line 19: | ||
==== Examples === | ==== 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. Note that these examples only aggregate counts, but any SQL aggregation function can be used. | + | 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. |
Line 25: | Line 25: | ||
<code> | <code> | ||
- | ----------------------------------- SUMMARY ----------------------------------- | + | ----------------------------------- SUMMARY ----------------------------------- |
- | Select count(*) \ | + | Select count(*) |
- | from LIST | + | from INDIVIDUALS; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: AGGREGATION, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Aggregate LIST_GEO using GEO02 for SUM(NUM_INDIVIDUALS) on 1 | + | Aggregate INDIVIDUALS_BY_DEMO using NUM_INDIVIDUALS for SUM(NUM_INDIVIDUALS); |
- | Return CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | + | Return CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); |
------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
</code> | </code> | ||
Line 42: | Line 41: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select count(*) \ | + | Select count(*) |
- | from LIST \ | + | from INDIVIDUALS |
- | where CITY = 'Boulder' | + | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000'; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: MDKQUAL, AGGREGATION, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET | + | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan |
- | Aggregate LIST_GEO using GEO02 for SUM(NUM_INDIVIDUALS) on 1 | + | 1, 1980"' and '"Dec 31, 2000"'; |
- | Return CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | + | Aggregate INDIVIDUALS_BY_DEMO using NUM_INDIVIDUALS for SUM(NUM_INDIVIDUALS); |
+ | Return CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
</code> | </code> | ||
Line 60: | Line 59: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select count(distinct STATE) \ | + | Select count(distinct GENDER) |
- | from LIST | + | from INDIVIDUALS; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: AGGREGATION, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Aggregate LIST_GEO using GEO02 for COUNT(DISTINCT STATE) on 1 | + | Aggregate INDIVIDUALS_BY_DEMO using GENDER for COUNT(DISTINCT GENDER) on 1; |
- | Return COUNT(DISTINCT LIST_GEO.STATE) | + | Return COUNT(DISTINCT INDIVIDUALS_BY_DEMO.GENDER); |
- | ------------------------------------------------------------------------------- | + | -------------------------------------------------------------------------------</code> |
- | + | ||
- | </code> | + | |
=== Simple count distinct with criteria === | === Simple count distinct with criteria === | ||
Line 77: | Line 73: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select count(distinct STATE) \ | + | Select count(distinct GENDER) |
- | from LIST \ | + | from INDIVIDUALS |
- | where CITY = 'Boulder' | + | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000'; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: MDKQUAL, AGGREGATION, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET | + | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan |
- | Aggregate LIST_GEO using GEO02 for COUNT(DISTINCT STATE) on 1 | + | 1, 1980"' and '"Dec 31, 2000"'; |
- | Return COUNT(DISTINCT LIST_GEO.STATE) | + | Aggregate INDIVIDUALS_BY_DEMO using GENDER for COUNT(DISTINCT GENDER) on 1; |
+ | Return COUNT(DISTINCT INDIVIDUALS_BY_DEMO.GENDER); | ||
------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
- | |||
</code> | </code> | ||
Line 96: | Line 91: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select STATE, \ | + | Select GENDER, |
- | count(*) \ | + | count(*) |
- | from LIST \ | + | from INDIVIDUALS |
- | group by STATE | + | group by GENDER; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: AGGREGATION, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1 | + | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), |
- | Return LIST_GEO.STATE, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | + | SUM(NUM_INDIVIDUALS); |
+ | Return INDIVIDUALS_BY_DEMO.GENDER, | ||
+ | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER); | ||
------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | ||
- | |||
</code> | </code> | ||
Line 115: | Line 110: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select STATE, \ | + | Select GENDER, |
- | count(*) \ | + | count(*) |
- | from LIST \ | + | from INDIVIDUALS |
- | where CITY = 'Boulder' \ | + | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000' |
- | group by STATE | + | group by GENDER; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: MDKQUAL, AGGREGATION, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET | + | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan |
- | Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1 | + | 1, 1980"' and '"Dec 31, 2000"'; |
- | Return LIST_GEO.STATE, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | + | 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> | </code> | ||
Line 136: | Line 132: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select STATES.DESCRIPTION, \ | + | Select GENDERS.DESCRIPTION, |
- | count(*) \ | + | count(*) |
- | from LIST \ | + | from INDIVIDUALS |
- | join STATES on LIST.STATE = STATES.STATE \ | + | join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER |
- | group by STATES.DESCRIPTION | + | group by GENDERS.DESCRIPTION; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: AGGREGATION, STARSCHEMA, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Build cache {C1} as (SELECT DESCRIPTION, STATE FROM STATES) | + | Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; |
- | Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1 | + | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), |
- | Retrieve {C1} using PK_STATE = LIST_GEO.STATE | + | SUM(NUM_INDIVIDUALS) on 1; |
- | Pass to queue {1} [STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)] | + | Retrieve {1} using PK_GENDER = INDIVIDUALS_BY_DEMO.GENDER; |
- | Sort {1} for GROUP BY [STATES.DESCRIPTION ASC] | + | Pass to queue {2} [GENDERS.DESCRIPTION, |
- | Retrieve {1} sequentially | + | CAST(SUM(INDIVIDUALS_BY_DEMO.NUM_INDIVIDUALS) AS UNSIGNED INTEGER)]; |
- | Return STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS 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> | </code> | ||
Line 161: | Line 158: | ||
<code> | <code> | ||
----------------------------------- SUMMARY ----------------------------------- | ----------------------------------- SUMMARY ----------------------------------- | ||
- | Select STATES.DESCRIPTION, \ | + | Select GENDERS.DESCRIPTION, |
- | count(*) \ | + | count(*) |
- | from LIST \ | + | from INDIVIDUALS |
- | join STATES on LIST.STATE = STATES.STATE \ | + | join GENDERS on INDIVIDUALS.GENDER = GENDERS.GENDER |
- | where CITY = 'Boulder' \ | + | where BIRTHDATE between 'Jan 1, 1980' and 'Dec 31, 2000' |
- | group by STATES.DESCRIPTION | + | group by GENDERS.DESCRIPTION; |
- | Version: 4.3 Build 4A (Compiled May 31 2007 12:13:16) | + | Version: 5.2.01 (Compiled Feb 2 2012 21:29:57) |
- | Optimization: MDKQUAL, AGGREGATION, STARSCHEMA, ROLLUP | + | Notes: Table INDIVIDUALS translated to Rollup Table INDIVIDUALS_BY_DEMO |
- | Notes: Table LIST translated to Rollup Table LIST_GEO | + | |
----------------------------------- DETAILS ----------------------------------- | ----------------------------------- DETAILS ----------------------------------- | ||
- | Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET | + | Qualify (INDIVIDUALS_BY_DEMO)INDIVIDUALS_BY_DEMO where BIRTHDATE between '"Jan |
- | Build cache {C1} as (SELECT DESCRIPTION, STATE FROM STATES) | + | 1, 1980"' and '"Dec 31, 2000"' on 1; |
- | Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1 | + | Build cache {1} as (SELECT DESCRIPTION, GENDER FROM GENDERS) on 2; |
- | Retrieve {C1} using PK_STATE = LIST_GEO.STATE | + | Aggregate INDIVIDUALS_BY_DEMO using AGG01 for GROUP(GENDER), |
- | Pass to queue {1} [STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)] | + | SUM(NUM_INDIVIDUALS) on 1; |
- | Sort {1} for GROUP BY [STATES.DESCRIPTION ASC] | + | Retrieve {1} using PK_GENDER = INDIVIDUALS_BY_DEMO.GENDER; |
- | Retrieve {1} sequentially | + | Pass to queue {2} [GENDERS.DESCRIPTION, |
- | Return STATES.DESCRIPTION, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER) | + | 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> | </code> | ||