16.20 - Example: Creating and Using an Aggregate Join Index - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

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;