17.00 - Example: Creating an Aggregate Join Index Using the MIN and MAX Functions - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
English (United States)
Last Update

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;