This example demonstrates a simple aggregate join index that uses the MIN and MAX functions defined on the join result of the base tables customer and order_tbl.
CREATE TABLE customer ( c_custkey INTEGER not null, c_name CHARACTER(26) CASESPECIFIC NOT NULL, c_address VARCHAR(41), c_nationkey INTEGER, c_phone CHARACTER(16), c_acctbal DECIMAL(13,2), c_mktsegment CHARACTER(21), c_comment VARCHAR(127)) UNIQUE PRIMARY INDEX( c_custkey );
CREATE TABLE order_tbl ( o_orderkey INTEGER NOT NULL, o_custkey INTEGER, o_orderstatus CHARACTER(1) CASESPECIFIC, o_totalprice DECIMAL(13,2) NOT NULL, o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_orderpriority CHARACTER(21), o_clerk CHARACTER(16), o_shippriority INTEGER, o_comment VARCHAR(79)) UNIQUE PRIMARY INDEX(o_orderkey);
You define the following aggregate join index ord_cust_idx on order_tbl and customer using the MIN and MAX functions.
CREATE JOIN INDEX ord_cust_idx AS SELECT c_nationkey, o_orderdate, MIN(o_totalprice) AS min_price, MAX(o_totalprice) AS max_price FROM order_tbl, customer WHERE o_custkey = c_custkey GROUP BY c_nationkey,o_orderdate ORDER BY o_orderdate;
The following query is provided as an example to show how the Optimizer rewrites the query to use ord_cust_idx to replace the base tables. The phrase SUM step represents any aggregate expression. This example performs a MIN aggregation on the aggregate join index, grouping on the c_nationkey column. An EXPLAIN of the SELECT statement includes a SUM step to aggregate from join index table ord_cust_idx with a condition of ( "(ord_cust_idx.O_ORDERDATE > DATE '1998-09-20') AND (ord_cust_idx.O_ORDERDATE < DATE '1998-10-15')").
SELECT COUNT(*), MIN(o_totalprice) FROM order_tbl, customer WHERE o_custkey = c_custkey AND o_orderdate > DATE ‘1998-09-20’ AND o_orderdate < DATE ‘1998-10-15’ GROUP BY c_nationkey;