Differences

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

--- design:rolluptable:intro [2009/10/29 03:47]
admin
+++ — (current)
@@ Line 1: / Line 1: @@
-====== Omnidex Rollup Tables ======
-====== Omnidex Rollup Tables ======
-Rollup tables are a new feature in Omnidex that can greatly improve the performance of some queries.  Rollup tables valuable in situations where data in a table is being repeatedly aggregated.  By pre-aggregating data in a table, they can save a great deal of normally performed against the main database.  The Omnidex Administrator simply evaluates the queries and identifies these types of situations, and then creates the rollup table with a simple SELECT statement.  Once included in the environment, Omnidex will automatically take advantage the rollup tables to improve performance whenever possible.
-Rollup tables are pre-aggregated summaries of individual tables that can be used to automatically satisfy Omnidex SQL queries with counts, distinct counts, sum, avg, min and max operations.
-Rollup tables are an excellent feature that can greatly improve performance on retrievals of very large.
-Rollup Tables
-  * Rollup tables are tables that pre-aggregate data from the underlying database.
-  * Rollup tables are declared in the environment file using a special syntax
-  * Rollup tables physically exist within the underlying database
-  * Rollup tables can be indexed with Omnidex
-  * There can be multiple rollup tables that pre-aggregate the same underlying table.
-  * Applications that perform a lot of aggregations can benefit from rollup tables
-  * Applications that perform a lot of counts can benefit from rollup tables
-  * Applications that use OmniSearch can benefit from rollup tables.
-  * Rollup tables are automatically used by the Omnidex SQL Engine to speed queries
-  * Queries from the application continue to reference the underlying table
-  * The SQL Engine determines whether the rollup table can be used
-  * All join columns, group by columns and criteria columns from the pre-aggregated table must exist in the rollup table
-  * All aggregations must be represented in the rollup table
-  * When multiple rollup tables can satisfy the query, the rollup with the lowest cardinality is used.
-  * Only one rollup table can be used per query
-  * Joins from the pre-aggregated table to other tables are allowed and will be redirected to the rollup table.
-  * Some complex queries are not optimized.  We do support outer joins, nested queries and set operations, but some SQL functions will prevent optimization.
-  * When possible, the SQL Engine redirects the query to the appropriate rollup table
-  * Rollup tables can be directly referenced in queries if desired, though this practice is not recommended so that rollup tables can be changed as needed without disabling the application.
-Since rollup tables are generally so much smaller than the original table, rollup tables can greatly speed queries that request aggregations.  Rollup tables are used seamlessly, meaning that when a query requests aggregations against the original table, Omnidex automatically looks at whether one or more rollup table could provide the answer.  If it finds eligible rollup tables, it automatically redirects the query to the preferred rollup table instead.
-Rollup tables can be significantly smaller than the original data.  For example, it is common to pre-aggregate a database based on standard geographic fields, such as state, county, postal code, city, area code, and so forth.  There are approximately 100,000 unique combinations of these columns in the United States, so a rollup table based on geography would have only 100,000 rows.  If a query can be redirected to this table, the response is instantaneous.
-For a rollup table to be used, all of the criteria columns and group by columns from the original table in the SQL statement must exist in the rollup table.  Furthermore, the aggregation functions in the SQL statement must be found in the rollup table.
-For example, if a rollup table contains the geographic columns mentioned above, accompanied by the counts of rows and counts of distinct households, then that rollup table can satisfy any SQL statement involving those geographic columns that has counts, counts of distinct households, or grouped counts.
-The rollup table can also satisfy queries that would join the original table to other tables, such as dimension or lookup tables as long as the join columns are in the rollup table and the joins would not affect the aggregations.  Only when references are made to non-geographic fields from that table, or references are made to any other aggregations, then the rollup table will not be used and the query will be processed against the original table.
-It is common to build several rollup tables, and their columns can overlap.  Omnidex will pick the smallest rollup table available that contains all of the columns referenced in the query.  Typically, it is only worthwhile to create rollup tables with 10 million rows or less, and it is preferable that rollup tables have 1 million rows or less.
-With Omnidex Grids, it is common to have rollup tables for an entire table, as well as individual rollup tables at each node.  The rollup tables for the entire table are placed in specialized nodes.  Rollup tables for the individual nodes simply exist in the node itself.
-==== Grid Considerations ====
-  * Rollup tables have special roles in grid scenarios
-  * Queries optimized against rollup tables should not be sent to the table partitions.
-  * Queries optimized against rollup tables are sent to special nodes that contain the rollup tables and do not contain the underlying tables.
-  * These tables are declared in the grid rule as nodes without clusters.
-  * It is possible to have high-level rollup tables for the whole grid while also having low-level rollup tables on each partition of the table
-Rollup tables are one of many features that make Omnidex a premiere solution for very large databases applications.
-==== Limitations ====
-  * Rollup tables have some limitations
-  * Rollup tables are currently read-only
-  * Rollup tables are currently limited to a aggregations of a single table
-Sample of environment file syntax
-<code>
-/*-------------------------------------------------------------------*/
-table "ORDERS"
-  physical "ord.dat"
-column "ACCT"            datatype INTEGER
-column "PRODUCT_NO"      datatype CHARACTER(4)
-column "ORDER_DATE"      datatype OMNIDEX DATE
-column "STATUS"          datatype CHARACTER(2)
-column "TAX_STATE"       datatype CHARACTER(2)
-column "SOURCE"          datatype TINYINT
-column "PMT_METHOD"      datatype TINYINT
-column "DISCOUNT"        datatype TINYINT
-column "QUANTITY"        datatype TINYINT
-column "SALES_TAX"       datatype FLOAT
-column "AMOUNT"          datatype FLOAT
-column "TOTAL"           datatype FLOAT
-/*-------------------------------------------------------------------*/
-table      "ORDERS_PROD_ROLLUP"
-  type     ROLLUP
-  physical "opr.dat"
-  as       "select     PRODUCT_NO, STATUS, SOURCE, PMT_METHOD,
-                       count(*) NUM_ORDERS,
-                       sum(QUANTITY) SUM_QUANTITY,
-                       sum(SALES_TAX) SUM_SALES_TAX,
-                       sum(TOTAL)SUM_TOTAL
-              from     ORDERS
-              group by PRODUCT_NO, STATUS, SOURCE, PMT_METHOD"
-/* Column listing is optional and is provided for readability */
-column "PRODUCT_NO"      datatype CHARACTER(4)
-column "STATUS"          datatype CHARACTER(2)
-column "SOURCE"          datatype TINYINT
-column "PMT_METHOD"      datatype TINYINT
-column "NUM_ORDERS"      datatype INTEGER
-column "SUM_QUANTITY"    datatype DOUBLE
-column "SUM_SALES_TAX"   datatype DOUBLE
-column "SUM_TOTAL"       datatype DOUBLE
-/*-------------------------------------------------------------------*/
-table      "ORDERS_ACCT_ROLLUP"
-  type     ROLLUP
-  physical "oar.dat"
-  as       "select     ACCT, STATUS, SOURCE, PMT_METHOD,
-                       count(*) NUM_ORDERS,
-                       sum(QUANTITY) SUM_QUANTITY,
-                       sum(SALES_TAX) SUM_SALES_TAX,
-                       sum(TOTAL)SUM_TOTAL
-              from     ORDERS
-              group by ACCT, STATUS, SOURCE, PMT_METHOD"
-/* Column listing is optional and is provided for readability */
-column "ACCT"            datatype INTEGER
-column "STATUS"          datatype CHARACTER(2)
-column "SOURCE"          datatype TINYINT
-column "PMT_METHOD"      datatype TINYINT
-column "NUM_ORDERS"      datatype INTEGER
-column "SUM_QUANTITY"    datatype DOUBLE
-column "SUM_SALES_TAX"   datatype DOUBLE
-column "SUM_TOTAL"       datatype DOUBLE
-</code>
-Samples of explain plan with redirection to rollup table
-Sample #1
-<code>
------------------------------------ SUMMARY -----------------------------------
-Select        PRODUCT_NO, \
-              count(*) \
-  from        ORDERS \
-  group by    PRODUCT_NO
-Version:      4.3 Build 3A  (Compiled Feb 16 2007  14:26:13)
-Optimization: AGGREGATION, ROLLUP
-Notes:        Table ORDERS translated to Rollup Table ORDERS_PROD_ROLLUP
------------------------------------ DETAILS -----------------------------------
-Aggregate ORDERS_PROD_ROLLUP using AGG for GROUP(PRODUCT_NO), SUM(NUM_ORDERS) \
-   on 1
- Return ORDERS_PROD_ROLLUP.PRODUCT_NO, COUNT('*')
--------------------------------------------------------------------------------
-</code>
-Sample #2
-<code>
------------------------------------ SUMMARY -----------------------------------
-Select        PRODUCTS.DESCRIPTION, \
-              ORDERS.PRODUCT_NO, \
-              count(*) \
-  from        ORDERS \
-  join        PRODUCTS on ORDERS.PRODUCT_NO = PRODUCTS.PRODUCT_NO \
-  join        SOURCES on ORDERS.SOURCE = SOURCES.SOURCE \
-  join        DIVISIONS on PRODUCTS.DIVISION = DIVISIONS.DIVISION \
-  where       SOURCES.DESCRIPTION = 'Telephone Order' and \
-              DIVISIONS.DESCRIPTION = 'Furniture' \
-  group by    PRODUCTS.DESCRIPTION, \
-              ORDERS.PRODUCT_NO
-Version:      4.3 Build 3A  (Compiled Feb 16 2007  14:26:13)
-Optimization: MDKQUAL, ROLLUP
-Warnings:     UNOPTIMIZED_AGGREGATION, UNOPTIMIZED_SORT
-Notes:        Table ORDERS translated to Rollup Table ORDERS_PROD_ROLLUP
------------------------------------ DETAILS -----------------------------------
-Qualify (DIVISIONS)DIVISIONS where DESCRIPTION = 'Furniture' on 1 with \
-   NOAUTORESET (Cached)
-   (1 DIVISIONS, 1 Pre-intersect   0.000 CPU    0.000 Elapsed)
-Join DIVISIONS using DIVISION to (PRODUCTS)PRODUCTS using DIVISION on 1 with \
-   NOAUTORESET (Cached)
-   (19 PRODUCTS, 19 Pre-intersect   0.000 CPU    0.000 Elapsed)
-Export to queue M1 on 1 with ODXSI,SORT (Cached)
-   (19 rows exported   0.000 CPU    0.000 Elapsed)
-Qualify (SOURCES)SOURCES where DESCRIPTION = '"Telephone Order"' on 1 with \
-   NOAUTORESET (Cached)
-   (1 SOURCES, 1 Pre-intersect   0.016 CPU    0.016 Elapsed)
-Join SOURCES using SOURCE to (ORDERS_PROD_ROLLUP)ORDERS_PROD_ROLLUP using \
-   SOURCE on 1 with NOAUTORESET (Cached)
-   (22 ORDERS_PROD_ROLLUP, 22 Pre-intersect   0.000 CPU    0.000 Elapsed)
-Qualify (ORDERS_PROD_ROLLUP)ORDERS_PROD_ROLLUP where and $ODXID = 'queue(M1, \
-)' on 1 with NOAUTORESET (Cached)
-   (2 ORDERS_PROD_ROLLUP, 12 Pre-intersect   0.000 CPU    0.000 Elapsed)
-Build cache {C1} as (SELECT DESCRIPTION, PRODUCT_NO FROM PRODUCTS)
-Fetchkeys $ROWID 1000 at a time on 1
- Retrieve ORDERS_PROD_ROLLUP using $ROWID = $ODXID
-  Retrieve {C1} using PK_PRODUCT_NO = ORDERS_PROD_ROLLUP.PRODUCT_NO
-  Pass to queue {1} [PRODUCTS.DESCRIPTION, ORDERS_PROD_ROLLUP.PRODUCT_NO, \
-     ORDERS_PROD_ROLLUP.NUM_ORDERS]
-Sort {1} for GROUP BY [PRODUCTS.DESCRIPTION ASC, \
-   ORDERS_PROD_ROLLUP.PRODUCT_NO ASC]
-   (2 rows   0.000 CPU    0.000 Elapsed)
-Retrieve {1} sequentially
-Return PRODUCTS.DESCRIPTION, ORDERS_PROD_ROLLUP.PRODUCT_NO, COUNT('*')
--------------------------------------------------------------------------------
-</code>
-Omnidex and Rollup Tables
-Introduction
-When working with very large database, there are lots of different techniques that can be used to improve performance.  Omnidex provides a variety of partitioning strategies that can be used to quickly reduce a large database to a small result set.   Omnidex provides specialized bitmap indexes geared for large databases.  A variety of other techniques, from multi-column indexing, to Hashed Data Caching, to reusing Omnidex ID lists, all help extract the best performance possible from queries against large databases.
-An additional technique is being added to Omnidex to speed performance on large databases.  Omnidex will now support the use of Rollup Tables.  Rollup Tables will allow certain queries that involve counts and aggregations to be processed significantly faster.
-What is a Rollup Table?
-Simply described, a rollup table is a physical table that pre-aggregates another table.  This idea originally had its origins in accounting, where specific general ledger categories were “rolled up” into broader categories, which were in turn “rolled up” into broader categories.  Ultimately, all categories “rolled up” into two categories: Income and Expense.  At each level of rollup, people could see the amounts summarized at that particular level.  Sometimes people wanted to see transactions at the detail level; other times they wanted see subtotals at the project or category level; and other times they wanted to see totals at a summary level.
-Any database table can be similarly rolled up.  While an accounting system summarizes dollar amounts, databases can summarize any information contained in the database.  An orders database can summarize order amounts, order quantities, discounts, taxes, credit limits, or any other information stored in the table.  This information can be pre-aggregated by product numbers or customer numbers.  Even tables without financial information can be rolled up.  A simple table of customers can be rolled up by geographic regions or personal traits, aggregating nothing other than the number of people within each geographic region or sharing a personal trait.   Rollups can contain any aggregation allowed in SQL, including COUNT, SUM, AVG, MIN and MAX.
-Once a rollup table has been created, queries can be redirected to the rollup table rather than the primary table.  The rollup table is much smaller than the primary table, so this can improve performance dramatically.  For example, obtaining the number of customers for each state can take a long time on a 300 million row table.  The 300 million rows must be processed, or if an index exists, then a 300 million row index must be processed.  If a rollup table exists that pre-aggregates by geographic region, this table will be significantly smaller.  In the United States, there are about 100,000 combinations of zip code, city, county, and state.  If the request for the number of customers for each state is redirected to this rollup table, it only involves processing 100,000 rows, which is 3,000 times fewer.  That directly translates to a query that performs 3,000 times faster.
-It can be valuable to have multiple rollups for the same primary table.  For example, a primary table of customers could be rolled up first by geography, and then again by personal traits such as gender, age and marital status.  One might ask why have multiple rollups rather than one larger rollup.  The more columns that are added to a rollup, generally the larger the table becomes.  If a rollup only contained gender, it would have only 2 rows.  If age were added, it would grow to around 200 rows.  If marital status were added, it could easily grow to 1000 rows.  By the time education, citizenship, call flags, bankruptcy flags and other demographics are added, our rollup table will have grown to nearly 1 million rows.  In order for a rollup table to provide its performance advantage, it must be only a fraction of the size of the original table.  A good rule of thumb would be to limit a rollup table to 1 million rows or 10% of the primary table, whichever is smaller.   If we combined all of the geographic information with all the personal traits, we would exceed this recommendation.
-One might then ask why not have a separate rollup for each column, such as a rollup for gender, a separate rollup for age and a separate rollup for marital status.  There are important advantages to having multiple columns in a rollup.  For a query to be redirected to a rollup table, all columns from the primary table that are referenced in the query must exist in the rollup table.  For example, obtaining the number of customers that are married or single, broken out by gender and age requires that all three of these columns exist in the rollup table.  If each column was in a separate rollup table, it would only be able to support the simplest of queries.
-This means that rollup tables require a good design, combining columns that are likely to be referenced together while insuring that the size of the table does not get too big.  Even with good design, rollup tables will not be able to service every query.  Many queries will still be processed against the primary table.  Rollup tables are designed to help with queries that regularly aggregate large amounts of data.  Because of this, analyzing the queries is always the first step in designing rollup tables, as that will reveal where the greatest opportunities lie.
-The following example shows a sample primary table with two rollup tables.  In this example, the rollup only pre-aggregates the counts of records, but they could also be expanded to pre-aggregate other number data.
-Sample Data for Primary Table and Two Rollup Tables
-PRIMARY TABLE
-Name	State	Zip	Gender	Age
-Alfred	CA	94122	M	27
-Catherine	CA	94123	F	29
-Elizabeth	CO	80301	F	29
-George	CA	94123	M	26
-Jane	CO	80301	F	26
-Jeff	CO	80304	M	27
-Jennifer	CA	94122	F	27
-Jim	CO	80301	M	27
-John	CO	80304	M	26
-Karen	CA	94122	F	29
-Linda	CA	94123	F	26
-Mark	CA	94122	M	27
-Mary	CA	94122	F	26
-Nancy	CO	80301	F	29
-Paul	CO	80301	M	26
-Peter	CA	94123	M	29
-Robert	CO	80301	M	27
-Sarah	CO	80304	F	26
-Susan	CA	94123	F	29
-William	CO	80301	M	26
-	GEOGRAPHIC ROLLUP TABLE
-State	Zip	Num_Individuals
-CA	94122	5
-CA	94123	5
-CO	80301	7
-CO	80304	3
-	PERSONAL TRAITS ROLLUP TABLE
-Gender	Age	Num_Individuals
-F	26	4
-F	27	1
-F	29	5
-M	26	4
-M	27	5
-M	29	1
-====== Designing Rollup Tables ======
-Rollup tables are a good tool for improving performance, but they are not always the best or only solution.  The Omnidex Administrator must consider the advantages and disadvantages of using rollup tables and determine when it is best to use them.  This is the same with as most performance options, including partitioning and indexing.  The table below lists some of the advantages and disadvantages of using rollup tables.
-====== Advantages	Disadvantages ======
-A rollup table will have fewer rows to read, so a query can be answered in a fraction of the time.  For instance, a table that preaggregates 300 million customers by zipcode and other geographic distinction will have less than 100,000 rows.  A query against 100,000 rows is much faster than a query against 300 million rows, regardless of indexing.	To achieve its fewer number of rows, a rollup table usually contains a dozen or fewer columns.   Only queries that are limited to these columns in their criteria and group by clauses can be redirected to the rollup table.
-A rollup table is generally quite small and does not require a lot of disk space or indexing time.	A rollup table itself does take time to build since it requires exhaustively reading and aggregating of the underlying data.
-A rollup table does not require any changes to the underlying data.	A rollup table is not automatically updated when the primary table is updated.  Instead, it is a snapshot of the data taken at the time it was generated.  It is reasonable to regenerate rollup tables on a daily or weekly basis, but rollup tables are not a good solution for up-to-the-minute queries against heavily-updated data.
-The best design approach for rollup tables begins with analyzing the queries and looking for repeated aggregations.  Within these situations, evaluate the following questions:
--	Is the value being aggregated a column value or the result of an expression?  At present, only column values from the primary table may be referenced in a rollup table.
--	Is the aggregation being grouped by one or more columns in that same table?  At present, only columns from the primary table may be referenced as group by columns in the rollup table.
--	Does the aggregation in the primary table substantially reduce the size of the result set?  Specifically, does the aggregation produce a result set that is either 1 million rows or 10% of the size of the non-aggregated result set?  A rollup table must be significantly smaller than the primary table to provide a performance advantage.
-After answering these questions, it should be possible to design one or more rollup tables that would be helpful for a given primary table.
-Creating and Declaring a Rollup Table
-A rollup table is quite easy to create.  The rollup tables are first created in the Omnidex environment catalog, complete with the SQL statement that represents the rollup data.  Then the UPDATE ROLLUPS command is issued in ODXSQL to populate the rollup tables.  Here is a simple example of creating a rollup that provides counts of people based on geographic regions:
-<code>
-table          "LIST_GEO"
-  type         ROLLUP
-  physical     "dat\list.geo"
-  as           "select COUNTRY, REGION, STATE, COUNTY, CITY, ZIP, MSA_CSA, PMSA,
-                count(distinct HOUSEHOLD) NUM_HOUSEHOLDS,
-                count(*) NUM_INDIVIDUALS
-                from LIST
-                group by COUNTRY, REGION, STATE, COUNTY, CITY, ZIP, MSA_CSA, PMSA"
-In ODXSQL, the following command will populate the rollup tables:
-	update rollups for database <database_name>
-	or
-	update rollups for table <table_name>
-</code>
-====== Indexing a Rollup Table ======
-Once the rollup tables have been created and declared, they may be indexed using Omnidex indexing.  There are no restrictions on indexing of rollup tables, and rollup tables can be indexed just like any other table.  Typically, they would be installed with both Omnidex MDK and Aggregation indexes.
-Since rollup tables are often quite small, it is common to heavily index the rollup table.  Ultimately, it is only necessary to index the rollup table in such a way that all queries that are redirected to the rollup table are fully optimized.  This can be determined by reviewing the query plan for each query that is redirected to the rollup table.  This is discussed in more detail later in this document.
-The following diagram shows the relationship of the primary table, the rollup tables and the Omnidex indexes.
-====== 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.
-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.
-<code>
-. Simple count
- ----------------------------------- SUMMARY -----------------------------------
-Select        count(*) \
-  from        LIST
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: AGGREGATION, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-Aggregate LIST_GEO using GEO02 for SUM(NUM_INDIVIDUALS) on 1
-Return CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
--------------------------------------------------------------------------------
-. Simple count with criteria
------------------------------------ SUMMARY -----------------------------------
-Select        count(*) \
-  from        LIST \
-  where       CITY = 'Boulder'
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: MDKQUAL, AGGREGATION, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
-Aggregate LIST_GEO using GEO02 for SUM(NUM_INDIVIDUALS) on 1
-Return CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
--------------------------------------------------------------------------------
-. Simple count distinct
------------------------------------ SUMMARY -----------------------------------
-Select        count(distinct STATE) \
-  from        LIST
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: AGGREGATION, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-Aggregate LIST_GEO using GEO02 for COUNT(DISTINCT STATE) on 1
-Return COUNT(DISTINCT LIST_GEO.STATE)
--------------------------------------------------------------------------------
-. Simple count distinct with criteria
------------------------------------ SUMMARY -----------------------------------
-Select        count(distinct STATE) \
-  from        LIST \
-  where       CITY = 'Boulder'
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: MDKQUAL, AGGREGATION, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
-Aggregate LIST_GEO using GEO02 for COUNT(DISTINCT STATE) on 1
-Return COUNT(DISTINCT LIST_GEO.STATE)
--------------------------------------------------------------------------------
-. Simple grouped count
------------------------------------ SUMMARY -----------------------------------
-Select        STATE, \
-              count(*) \
-  from        LIST \
-  group by    STATE
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: AGGREGATION, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1
-Return LIST_GEO.STATE, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
--------------------------------------------------------------------------------
-. Simple grouped count with criteria
------------------------------------ SUMMARY -----------------------------------
-Select        STATE, \
-              count(*) \
-  from        LIST \
-  where       CITY = 'Boulder' \
-  group by    STATE
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: MDKQUAL, AGGREGATION, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-Qualify (LIST_GEO)LIST_GEO where CITY = 'Boulder' on 1 with NOAUTORESET
-Aggregate LIST_GEO using GEO02 for GROUP(STATE), SUM(NUM_INDIVIDUALS) on 1
-Return LIST_GEO.STATE, CAST(SUM(LIST_GEO.NUM_INDIVIDUALS) AS INTEGER)
--------------------------------------------------------------------------------
-. Count grouped by joined table
------------------------------------ SUMMARY -----------------------------------
-Select        STATES.DESCRIPTION, \
-              count(*) \
-  from        LIST \
-  join        STATES on LIST.STATE = STATES.STATE \
-  group by    STATES.DESCRIPTION
-Version:      4.3 Build 4A  (Compiled May 31 2007  12:13:16)
-Optimization: AGGREGATION, STARSCHEMA, ROLLUP
-Notes:        Table LIST translated to Rollup Table LIST_GEO
------------------------------------ DETAILS -----------------------------------
-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)
--------------------------------------------------------------------------------
-. Count grouped by joined table with criteria
------------------------------------ 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>
-====== Quick Links ======
-{{page>:quicklinks&nofooter&noeditbtn}}

Back to top

design/rolluptable/intro.1256788066.txt.gz · Last modified: 2012/10/26 14:25 (external edit)

Sitemap Recent Changes