Example: Creating an Aggregate Join Index Using the MIN and MAX Functions - 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 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;