Example: Creating and Using an Aggregate Join Index - Advanced SQL Engine - Teradata Database

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:

1. Redistribute orders into a spool file.
2. Sort the spool file on o_custkey.
3. Merge join the sorted spool file and the customer file.
4. 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;