17.00 - Example: Creating a Hash Index According to Shipping Date - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
September 2020
Content Type
Programming Reference
Publication ID
English (United States)

The following hash index is distributed explicitly on l_shipdate and stored explicitly in value order on l_shipdate:

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

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

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

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