Example: Creating a Join Index with a Period Column in the Select List - Teradata VantageCloud Lake

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.

     CREATE JOIN INDEX ji_pdt AS
       SELECT b1, c1, d1
       FROM t1;

If you then submit a HELP JOIN INDEX request on ji_pdt , Vantage 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).