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/17 22:16] admin |
— (current) | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== Rollup Tables ====== | ||
| - | ===== 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 ====== | ||
| - | ^ [[:start|Quick Links]] ^^^^^ | ||
| - | ^ General ^ Programs ^ Interfaces ^ General Topics ^ Miscellaneous Items^ | ||
| - | | [[overview:intro|Omnidex Introduction]] | [[programs:odxsql:intro|OdxSQL]] |[[development:interfaces:odbc:intro|ODBC]] | [[design:grids:intro|Grids]] | [[releasenotes:intro|Release Notes]] | | ||
| - | | [[odxadmin:intro|Administrating Omnidex (OdxAdmin)]] | [[programs:odxnet:intro|OdxNet]] |[[development:interfaces:jdbc:intro|JDBC]] | [[design:unionview:intro|Union Views]]| [[osinstall:intro|Server Installs]] | | ||
| - | | [[design:intro|Data and Index Design]] | [[programs:odxaim:intro|OdxAim]] |[[development:interfaces:storedproc:intro|Stored Procedures]] | [[design:rolluptable:intro|Rollup Tables]] | [[osinstall:licensing:intro|Licensing]] | | ||
| - | | [[sql:intro|Omnidex Queries & SQL]] | [[programs:dbinstal:intro|DBInstal]] |[[development:interfaces:omniaccess:intro|OmniAccess]] | [[design:partitioning:intro|Partitioning]] | [[osinstall:settings:intro|Settings]] | | ||
| - | | [[activecounts:intro|Active Counts]] | [[programs:oaenv:intro|OaComp/decomp/helper]] |[[development:interfaces:clientoa:intro|Client OA]] | [[development:explainplan:intro|Explain Plans]]| [[glossary:intro|Glossary]] | | ||
| - | | [[powersearch:intro|PowerSearch]] | [[programs:client:intro|DSEdit/OdxQuery]] | [[rdbms:intro|RDBMS Indexing]] | [[development:debugging:intro|Debugging]] | [[appendix:intro|Appendix]] | | ||
| - | (c) Copyright Dynamic Information Systems - This document was last updated July 15, 2009. | ||
| - | |||
| - | ---- | ||