Differences

This shows you the differences between two versions of the page.

--- 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>
- ----------------------------------- 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 -----------------------------------
-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>
@@ Line 42: / Line 41: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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
+, 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>
@@ Line 60: / Line 59: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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 ===
@@ Line 77: / Line 73: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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
+, 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>
@@ Line 96: / Line 91: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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>
@@ Line 115: / Line 110: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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
+, 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>
@@ Line 136: / Line 132: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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>
@@ Line 161: / Line 158: @@
 <code>
 ----------------------------------- 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 -----------------------------------
-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)
+, 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>

Back to top

admin/features/rollups/optimization.1329497177.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes