17.10 - Example: Creating a Join Index With a Single-Level Partitioned Primary Index - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Assume the following base table definition:

     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, o_orderdate);

The following SQL text creates a single-level PPI join index on the base table named orders. Because the o_totalprice column has a data type of DECIMAL(13,2), o_totalprice values can have more digits than an INTEGER type can handle, so you might see errors when you insert values into o_totalprice because the definition of ordJI1 casts o_totalprice values as INTEGER values in its partitioning expression.

     CREATE JOIN INDEX ordJI1 AS 
       SELECT o_custkey, o_totalprice 
       FROM orders 
     PRIMARY INDEX (o_custkey)
     PARTITION BY RANGE_N(CAST(o_totalprice AS INTEGER)
                  BETWEEN  0 
                  AND 999999 
                  EACH   100, NO RANGE);