17.10 - ORDER BY - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

Row ordering on each AMP by a single NUSI column by value or hash.

If you specify ORDER BY, but do not specify VALUES or HASH, the ordering defaults to VALUES.

If you do not specify ORDER BY, the system orders the rows on their hash values.

If you specify HASH, the system orders the rows on their hash values.

In an ORDER BY clause, you cannot specify a column with an XML, Geospatial, JSON, or DATASET data type.

VALUES
Value-ordering for the ORDER BY NUSI column.
Select VALUES to optimize queries that return a contiguous range of values, especially for a covering index or a nested join.
You can specify VALUES with or without an order column name. If you do not specify order_column_name, the system orders the NUSI on the values of its first column.
HASH
Hash-ordering for the ORDER BY column.
Select HASH to limit hash-ordering to one column, rather than all columns. This is the default if you do not specify an ORDER BY clause.
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 HASH with or without an order column name. If you do not specify order_column_name, the system orders the NUSI on the hash of its first column.
order_column_name
An optional column in the INDEX column list for a NUSI that specifies the sort order used to store index rows.
If you do not specify an order_column_name, the system orders the NUSI rows using the first column in the index definition by default.
A value-ordered order_column_name can have any of the following data types:
  • BYTEINT
  • DATE
  • DECIMAL
  • INTEGER
  • SMALLINT
Values for order_column_name are limited to 4 or fewer bytes.