15.10 - Example: Creating a Hash Index According to Ship Date - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

The following hash index is distributed implicitly on l_orderkey, the primary index for the base table lineitem, and stored explicitly in value order on l_shipdate:

     CREATE HASH INDEX line_hidx_5 (l_shipdate) ON lineitem
     ORDER BY (l_shipdate);

Equivalently, you could specify ORDER BY VALUES instead of ORDER BY.

Note that the ORDER BY column list complies with the rules that it be limited to a single column, that the column must be in the column_name_1 list, must be a numeric type, and must be four or fewer bytes.

This creates an index that is equivalent in structure to the following join index:

     CREATE JOIN INDEX line_jidx_5 AS
      SELECT (l_shipdate), (l_orderkey, lineitem.ROWID)
      FROM lineitem
      ORDER BY l_shipdate
     PRIMARY INDEX (l_orderkey);