Example: Defining Secondary Indexes on UDT Columns - Teradata Vantage - Analytics Database

These examples show how UDT columns can be used to define unique and nonunique secondary indexes for a table or 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;

Assume you define the following tables, where tbl_integer and tbl_char50 are both UDT data types. Note that the UPI for table_1 and the NUPI for table_2 are also defined on UDT columns.

     CREATE TABLE table_1 (
       id       tbl_integer, 
       emp_name tbl_char50) 
     UNIQUE PRIMARY INDEX(id);
     CREATE TABLE table_2 (
       id         tbl_integer, 
       emp_name   tbl_char50, 
       start_date DATE) 
     INDEX(emp_name);

The following USI is defined on the UDT column emp_name of table_1.

CREATE UNIQUE INDEX idx_1(emp_name) ON table_1;      

The following NUSI is defined on the UDT columns emp_name and start_date of table_2.

CREATE INDEX idx_2(emp_name, start_date) ON table_2;