15.10 - Example: Creating a Join Index With a Period Column in its Select List - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
Release Date
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

This example creates a join index with a Period column, d1, in its select list.

Assume you have created table t1 with the following definition.

     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER,
       c1 INTEGER,
       d1 PERIOD(DATE),
       e1 DATE
       f1 CHARACTER(100));

You can create a join index on this table that incorporates the Period column d1 as follows.

       SELECT b1, c1, d1
       FROM t1;

If you then submit a HELP JOIN INDEX request on ji_pdt , Teradata Database returns a report like the following.

     HELP JOIN INDEX ji_pdt;
*** Help information returned. 3 rows.
*** Total elapsed time was 1 second.
Column Name                Type      Comment
-------------------------- --------- ---------------------
b1                          I        ?
c1                          I        ?
d1                          PD       ?

The Optimizer can use column d1 in ji_pdt to map all query expressions that involve t1.d1 to the join index. For example, it can map BEGIN(t1.d1) to BEGIN(ji_pdt.d1).