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 [2012/02/17 16:46] doc |
admin:features:rollups:optimization [2016/06/28 22:38] (current) |
||
---|---|---|---|
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> | ||