15.10 - Example: Creating a Join Index With a P_INTERSECT Expression in the Select List - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

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

This example creates a join index with a P_INTERSECT Period expression in its select list.

     CREATE JOIN INDEX ji_p_intersect AS 
       SELECT b1,c1,d1 P_INTERSECT PERIOD(DATE '2010-08-01', 
                                          DATE '2011-08-01') AS prd 
       FROM t1;

If you then submit a HELP JOIN INDEX request on ji_p_intersect, the system returns a report like the following:

     HELP JOIN INDEX ji_p_intersect;
Column Name                Type      Comment
-------------------------- --------- ---------------------
b1                         I         ?
c1                         I         ?
prd                        PD        ?

Similar to Example: Creating a Join Index With a BEGIN Bound Function in its Select List and Example: Creating a Join Index With a Multicolumn CASE Expression in its Select List, column prd in this example can be used to map identical query expressions to the join index. In addition, ji_p_intersect.prd can be used for the following mappings:

  • The expression t1.d1 P_INTERSECT <period>, where <period > is a constant, can be mapped to expression ji_p_intersect.prd P_INTERSECT <period> if <period> is within the defined interval ('2010-08-01', '2011-08-01').
  • The range condition END(t1.d1) >= <prd_start> AND BEGIN(t1.d1) <= <prd_end>, where <prd_start> and <prd_end> are constants, can be mapped to END(ji_p_intersect.prd) >= <prd_start> AND BEGIN(ji_p_intersect.PRD) if PERIOD(<prd_start>, <prd_end>) is within the defined interval ('2010-08-01', '2011-08-01').