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

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