16.20 - Example: Creating a Join Index With a Period Column in its Select List - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

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 , 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).