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.

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