Differences

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

--- admin:features:rollups:optimization [2011/01/24 15:26]
127.0.0.1 external edit
+++ admin:features:rollups:optimization [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
 ~~NOTOC~~
 {{page>:top_add&nofooter&noeditbtn}}
@@ Line 6: / Line 7: @@
 ===== 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 18: / Line 19: @@
 ==== Examples ===
-The following are examples of queries that can be redirected to the rollup table using the example show 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 24: / 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 41: / 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 59: / 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 76: / 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 95: / 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 114: / 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 135: / 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 160: / 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.1295882813.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes