15.00 - Specifying the Primary Index for a Hash Index - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Specifying the Primary Index for a Hash Index

Like any base table or join index, a hash index must have a primary index. The only exceptions are NoPI tables (see “Unpartitioned NoPI Tables” on page 575), column‑partitioned tables and join indexes (see “Column‑Partitioned Join Indexes” on page 354 and “Column‑Partitioned Tables” on page 577), and global temporary trace tables (see “CREATE GLOBAL TEMPORARY TRACE TABLE” on page 317). Unlike other base tables, you do not use the keywords PRIMARY INDEX to specify the primary index for a hash index.

The following table explains how the primary index for a hash index is defined:

 

IF you …

THEN the system creates the primary index for the hash index using this column set …

do not define a BY clause

the primary index of the base table to which the hash index refers.

specify a BY clause and an ORDER BY clause

the column set specified in the column_name_2 set in the BY clause.

These columns must also be defined in the column_name_1 set.

Rows in a hash index defined with this type of primary index are ordered either by row hash or by numeric value, depending on what you specify in the ORDER BY clause.