ordering_clause - Advanced SQL Engine - Teradata Database

Row ordering on each AMP by a single NUSI column: either value-ordered or hash-ordered.

If you specify HASH, the rows are hash-ordered on the AMP. Otherwise, the rows are value-ordered.

Each multicolumn NUSI created with an ORDER BY clause counts as two consecutive indexes against the maximum of any mix of 32 secondary, hash, and join indexes that can be defined per table. This includes the system-defined secondary indexes used to implement PRIMARY KEY and UNIQUE constraints for nontemporal tables and the single-table join indexes used to implement PRIMARY KEY and UNIQUE constraints for temporal tables. For details, see Teradata Vantage™ - Temporal Table Support , B035-1182 . See the CREATE INDEX topic “Why Consecutive Indexes Are Important For Value-Ordered NUSIs” Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

If you do not specify an ORDER BY clause, the system automatically hash orders index rows on their columns.

You cannot order by the system-derived PARTITION or PARTITION#L n columns. However, you can specify a user-defined column named partition.

You can order by HASH on a UDT column, but not by VALUE.

ORDER BY is not applicable to geospatial indexes.

VALUES

Value-ordering for the ORDER BY column.

Select VALUES to optimize queries that return a contiguous range of values, especially for a covered index or a nested join.

You cannot specify ORDER BY VALUES on a UDT column.

VALUES is not applicable to geospatial indexes.

HASH

Hash-ordering for the ORDER BY column.

Select HASH to limit hash-ordering to one column.

Hash-ordering a multicolumn NUSI on one of its columns allows the NUSI to participate in a nested join where join conditions involve only that ordering column.

You can specify ORDER BY HASH on a UDT column.

HASH is not applicable to geospatial indexes.

order_column_name

Column in the index_column_name list that specifies the sort order to be used.

An order column in a secondary index cannot have a Period, BLOB, CLOB, XML, LOB UDT, Geospatial, ARRAY/VARRAY, or VARIANT_TYPE data type.

You cannot include the system-derived columns PARTITION or PARTITION#L n in the order_column_name list.

However, you can specify a user-defined column named PARTITION or PARTITION#L n, where n ranges from 1 through 62.

Supported data types for a value-ordered, four-bytes-or-less order_column_name are the following:

• BYTEINT
• DATE
• DECIMAL
• INTEGER
• SMALLINT

If you specify ORDER BY without also specifying an explicit order_column_name, then the system uses the first column specified in the index_column_name list to order the rows in the index.