This example set uses the following table definitions:
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 orders ( 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);
Consider the following aggregate join query.
SELECT COUNT(*), SUM(o_totalprice) FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate > DATE '1998-09-20' AND o_orderdate < DATE '1998-10-15' GROUP BY c_nationkey;
Without an aggregate join index, a typical execution plan for this query might involve the following stages:
- Redistribute orders into a spool file.
- Sort the spool file on o_custkey.
- Merge join the sorted spool file and the customer file.
- Aggregate the result of the merge join.
Suppose you define the following aggregate join index, which aggregates o_totalprice over a join of orders and customer :
SELECT c_nationkey, SUM(o_totalprice(FLOAT)) AS price, o_orderdate FROM orders, customer WHERE o_custkey = c_custkey GROUP BY c_nationkey, o_orderdate ORDER BY o_orderdate;
The execution plan produced by the Optimizer for this query includes an aggregate step on the aggregate join index, which is much smaller than either one of the join tables. 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')"), grouped by c_nationkey.
EXPLAIN SELECT COUNT(*), SUM(o_totalprice) FROM orders, customer WHERE o_custkey = c_custkey AND o_orderdate > DATE '1998-09-20' AND o_orderdate < DATE '1998-10-15' GROUP BY c_nationkey;