Example: Creating and Using an Aggregate Join Index - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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;