15.00 - Value Ordering and Range Queries - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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);