Row ordering on each AMP: either value-ordered or hash-ordered.
If you do not specify an ORDER BY clause, then the hash index rows have the same ordering as that specified for the base table on which the index is defined.
You must specify an ORDER BY clause that includes an explicit column list for a hash index defined on a partitioned primary index table.
If you specify a BY clause, you must specify an ORDER BY clause for the hash index definition.
If you specify neither a BY clause nor an ORDER BY clause, Teradata Database distributes the hash index rows by the hash of the primary index column set of the base table on which they are defined.
As a result, the hash index is AMP-local and its rows are ordered by the hash of those columns. This is also true if the primary index of the base table is defined on a UDT column.
If you specify the system-derived PARTITION column or the system-derived PARTITION#Ln columns, where n is a number from 1 through 62, the request returns an error to the requestor.
You can specify a user-defined column named partition or partition#Ln.
If you specify ORDER BY VALUES with a column_name_3 list, the column_name_3 list is limited to a single numeric column with a size of 4 or fewer bytes.
In this case, the primary index of the base table must consist of a single column having a length of 4 or fewer bytes.
The base table primary index column does not have to be included in the column_name_1 set.
You cannot specify ORDER BY VALUES on a UDT column.
The hash index rows are stored in ascending order by the values of the column specified as the primary index of the base table.
The values specified for the column_name_3 list must have one of the following data types:
- BYTEINT
- DATE
- DECIMAL
- INTEGER
- SMALLINT
If you specify ORDER BY HASH, you must specify a BY clause and the columns specified for the column_name_2 and column_name_3 sets must be identical.
The hash index rows are ordered on the row hash value of these columns.
You can specify ORDER BY HASH on a UDT column.
If you specify ORDER BY without specifying either the HASH or the VALUES keywords, Teradata Database orders the rows by the default, which is VALUES.
In this case, you must specify a column_name_3 list.
See SQL Data Manipulation Language, B035-1146 for complete documentation of the ORDER BY clause.