16.20 - Example: Creating a Join Index With a Multicolumn CASE Expression in its Select List - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update

This example uses table t1 as defined in Example: Creating a Join Index With a Period Column in its Select List as well as the following tables to create a join index with a multicolumn CASE expression in its select list.

     CREATE TABLE t2 (
       a2 INTEGER,
       b2 INTEGER,
       c2 INTEGER,
       d2 PERIOD(DATE),
       e2 DATE,
       f2 CHARACTER(100))
     UNIQUE INDEX(b2);
     CREATE TABLE t3 (
       a3 INTEGER,
       b3 INTEGER,
       c3 INTEGER);
     CREATE JOIN INDEX ji_case AS 
       SELECT a1,b1,c1,CASE 
                         WHEN t1.b1=t2.b2 
                         THEN t2.a2 
                         ELSE t3.a3 
                       END AS a
       FROM t1,t2,t3 
       WHERE a1=a2 
       AND a2=a3;

If you then submit a HELP JOIN INDEX request on ji_case, Teradata Database returns a report like the following:

     HELP JOIN INDEX ji_case;
*** Help information returned. 3 rows.
*** Total elapsed time was 1 second.
Column Name                Type      Comment
-------------------------- --------- ---------------------
a1                         I         ?
b1                         I         ?
c1                         I         ?
a                          I         ?

As was done for column b in Example: Creating a Join Index With a BEGIN Bound Function in its Select List, column a in this example can be used to map an identical query expression to the join index.

Multicolumn expressions in a join index definition affect its maintenance. For ji_case, the value of column a depends on t1.b1 , t2.a2 , t2.b2, and t3.a3 , so join index maintenance is required whenever any of those columns is updated, and the join index update uses a spool file to resolve the expression. For example, a simple update on base table t1 like the following request cannot be mapped to a direct update on ji_case, although the delete condition a1=10 can be evaluated directly on the join index.

     UPDATE t1 
       SET b1=10 
       WHERE a1=10;