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