15.10 - ORDER BY, Index Definition - 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: either value-ordered or hash-ordered.

You cannot order rows on a UDT, BLOB, CLOB, JSON, XML, Period, or Geospatial column.

See SQL Fundamentals, B035-1141 for the rules for naming database objects.

Rules for using an ORDER BY clause are shown in the following table.

  • If you specify ORDER BY with VALUES, then the ORDER BY column_name must be numeric with four bytes or less.
  • If you specify ORDER BY without also specifying either HASH or VALUES, then Teradata Database assumes VALUES by default.
  • If you specify ORDER BY with a column name, then the ORDER BY column_name must be one of the columns in the INDEX list of columns.
VALUES
Value-ordering for the ORDER BY column.
Select VALUES to optimize queries that return a contiguous range of values, especially for a covering index or a nested join.
HASH
Hash-ordering for the ORDER BY column.
Select HASH to limit hash-ordering to one column, rather than all columns, which is the default.
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.
order_column_name
A column in the INDEX list of columns that specifies the sort order to be used.
Supported data types for a value-ordered, four-bytes-or-less order_column_name are the following.
  • BYTEINT
  • DATE
  • DECIMAL
  • INTEGER
  • SMALLINT