ORDER BY, Index Definition - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples
Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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

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

See Teradata Vantage™ - 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 Vantage 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.
You can specify the following data types for a value-ordered, four-bytes-or-less order_column_name:
  • BYTEINT
  • DATE
  • DECIMAL
  • INTEGER
  • SMALLINT