17.10 - Value-Ordered Storage of Join Index Rows - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Last Update
2021-07-27
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)
Last Update
2021-07-27

The distribution of the subtable rows for a join index across the AMPs is controlled by its NUPI. By default, join index subtable rows are sorted locally on each AMP by the row hash value of the NUPI column set. You can also specify that rows be stored in value-order.

Value Ordering and Range Queries

You can specify value ordering by means of the optional ORDER BY clause in the join index definition. Sorting a join index by values, as opposed to row hash values, is especially useful for range queries involving the sort key. Value ordering is limited to a single numeric or DATE columns with a size of 4 bytes or less.

For example, the following join index rows are hash-distributed using c_name and are value-ordered on each AMP using c_custkey as the sort key.

     CREATE JOIN INDEX OrdCustIdx AS
      SELECT (o_custkey
             ,c_name)
             ,
             (o_status
             ,o_date
             ,o_comment)
     FROM Orders LEFT JOIN Customer
     ON o_custkey = c_custkey
     ORDER BY o_custkey
     PRIMARY INDEX (c_name);