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

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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;