Example: Creating Join Indexes With a UDT Column as a Primary or Secondary Index - Advanced SQL Engine - Teradata Database

These examples show how UDT columns can be used to define primary and secondary indexes for a join index.

First, define the UDTs that are used for the example columns.

The type tbl_integer is a distinct UDT based on the INTEGER data type.

     CREATE TYPE tbl_integer AS INTEGER FINAL;

The type tbl_char50 is also a distinct UDT and is based on the CHARACTER data type.

     CREATE TYPE tbl_char50 AS CHARACTER(50) FINAL;

Then, you create the following tables.

     CREATE TABLE t1_jupi (
       id       tbl_integer, 
       emp_name tbl_char50) 
     UNIQUE PRIMARY INDEX(id);
     CREATE TABLE t1_jusi (
       emp_name tbl_char50, 
       id       tbl_integer) 
     UNIQUE INDEX(id);

The first example creates an uncompressed join index with both a NUPI and a NUSI defined on a UDT column.

     CREATE JOIN INDEX t1_jindx AS 
       SELECT t1_jupi.id AS id_1, t1_jupi.emp_name AS name_1, 
              t1_jusi.id AS id_2, t1_jusi.emp_name AS name_2 
       FROM t1_jupi, t1_jusi 
       WHERE t1_jupi.id = t1_jusi.id 
     PRIMARY INDEX (id_1), 
     INDEX (id_2);

The second example creates a row-compressed join index with both a NUPI and a NUSI defined on a UDT column. In this example, the repeating column set is t1_jusi.id and t1_jusi.emp_name. The fixed column set is t1_jupi.id and t1_jupi.emp_name.

     CREATE JOIN INDEX t3_jindx AS 
       SELECT (t1_jusi.id AS id_2, t1_jusi.emp_name AS name_2) 
              (t1_jupi.id AS id_1, t1_jupi.emp_name AS name_1), 
              
       FROM t1_jusi,  t1_jupi
       WHERE t1_jusi.id =  t1_jupi.id
     PRIMARY INDEX(id_1), 
     INDEX(id_2);

The third example creates a join index with a UPI defined on a UDT column.

     CREATE JOIN INDEX t1_upi_jindx AS 
       SELECT t1_jupi.id AS id_1, t1_jupi.emp_name AS name_1 
       FROM t1_jupi 
     UNIQUE PRIMARY INDEX (name_1);