Rollup Tables

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.

• 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 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

/*-------------------------------------------------------------------*/
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('*')
-------------------------------------------------------------------------------