Example: Hash Indexes With a UDT Column in their Column Lists - Teradata Vantage - Analytics Database

These examples show how UDT columns can be specified in the column_name_list1, column_name_list2, and column_name_list3 for a hash index.

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

TBL_INTEGER is a distinct UDT based on the INTEGER data type.

     CREATE TYPE TBL_INTEGER AS INTEGER FINAL;

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 table.

     CREATE TABLE t1_hash_index (
       id       TBL_INTEGER, 
       emp_name TBL_CHAR50) 
     PRIMARY INDEX(id);

The first hash index definition specifies the UDT column emp_name in all three of its column lists.

     CREATE HASH INDEX tb_index_1 (emp_name) 
     ON t1_hashindex 
     BY (emp_name) 
     ORDER BY HASH (emp_name);

The second hash index definition specifies the UDT column emp_name in its column_name_1 list.

This example shows how a hash index created without defining either a BY or ORDER BY clause is valid and its rows are distributed by the hash of the primary index of the base table on which it is defined when the primary index of the table is created on a UDT column.

     CREATE HASH INDEX tb_index_2 (emp_name) 
     ON t1_hashindex;