This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
design:rolluptable:intro [2009/07/23 01:47] admin |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Rollup Tables ====== | ||
| - | Rollup tables are another Omnidex feature that can greatly improve performance. Rollup tables are pre-aggregated summaries of individual tables that can be used to satisfy queries with counts, distinct counts, sum, avg, min and max operations. 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. | ||
| - | |||
| - | ===== Overview ===== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | ==== 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 | ||
| - | |||
| - | ==== 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, \ | ||
| - | 1)' 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> | ||
| - | |||
| - | ====== Quick Links ====== | ||
| - | {{page>:quicklinks&nofooter&noeditbtn}} | ||