This is an old revision of the document!
Rollup Tables
- Rollup tables are tables that pre-aggregate data from the underlying database. o Rollup tables are declared in the environment file using a special syntax o Rollup tables physically exist within the underlying database o Rollup tables can be indexed with Omnidex o There can be multiple rollup tables that pre-aggregate the same underlying table.
- Rollup tables can be used in a variety of applications o Applications that perform a lot of aggregations can benefit from rollup tables o Applications that perform a lot of counts can benefit from rollup tables o Applications that use OmniSearch can benefit from rollup tables.
- Rollup tables are automatically used by the Omnidex SQL Engine to speed queries o Queries from the application continue to reference the underlying table o 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. o 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.
- Rollup tables have special roles in grid scenarios o Queries optimized against rollup tables should not be sent to the table partitions. o 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. o 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 o Work with Eric when setting up a grid.
- Rollup tables have some limitations o Rollup tables are currently read-only o Rollup tables are currently limited to a aggregations of a single table
Sample of environment file syntax
/*-------------------------------------------------------------------*/
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
Samples of explain plan with redirection to rollup table
Sample #1
----------------------------------- 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('*')
-------------------------------------------------------------------------------
Sample #2
----------------------------------- 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('*')
-------------------------------------------------------------------------------
© Copyright Dynamic Information Systems - This document was last updated July 15, 2009.