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.