Example: Creating a Join Index with a BEGIN Bound Function in the Select List - Teradata Vantage

This example uses table t1, defined in Example: Creating a Join Index with a Period Column in the Select List, to create a join index with a BEGIN bound function in its select list.

     CREATE JOIN INDEX ji_begin AS 
       SELECT b1, c1, BEGIN(d1) AS b 
       FROM t1;

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

     HELP JOIN INDEX ji_begin;

Column Name                Type      Comment
-------------------------- --------- ---------------------
b1                         I         ?
c1                         I         ?
b                          DA        ?

Column b in ji_begin can be used to map an identical query expression such as BEGIN(d1).

For example, the Optimizer can take the following request:

     SELECT b1, c1, BEGIN(d1) 
     FROM t1; 

and map it to this request that uses the join index:

     SELECT b1, c1, b 
     FROM ji_begin;

Or it can take the following request:

     SELECT b1,c1 
     FROM t1 
     WHERE BEGIN(d1) < '2008-05-01';

and map it to this request that uses the join index:

     SELECT b1,c1 
     FROM ji_begin 
     WHERE b < '2008-05-01';