Example: Creating a Join Index With a BEGIN Bound Function in its Select List - Teradata Vantage - Analytics Database

This example also uses table t1 as defined in Example: Creating a Join Index With a Period Column in its 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';