15.10 - ORDER BY - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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

You cannot specify a column with a JSON or an XML data type in an ORDER BY phrase.

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.

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.