Example: Hash Indexes With a UDT Column in their Column Lists - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;