INDEX - Advanced SQL Engine - Teradata Database

A set of nonunique secondary indexes defined on the join index.

index_name

The optional name of the NUSI being defined. For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

ALL

The defined NUSI is to maintain Row ID pointers for each logical row of the join index, instead of only the compressed physical rows.

ALL also ignores the NOT CASESPECIFIC attribute of data types so a NUSI can include case-specific values.

ALL enables a NUSI to cover a join index, which enhances performance by eliminating the need to access the join index when all values needed by a query are in the secondary index. However, ALL might also require the use of additional index storage space.

Use this keyword only when a secondary index is being defined on top of a join index.

You cannot specify ALL with a PRIMARY index.

You cannot specify multiple indexes that differ only by the presence or absence of the ALL option.

index_column_name

A column set whose values are to be an index on this join index. If you specify more than one column, the new index is based on the combined values of each column.

A maximum of 64 columns can be defined for one index.

Multiple indexes can be defined on the same columns as long as each index differs in its ordering option, for example, VALUES or HASH.

If two specified columns have the same name, both names must be aliased with unique column name aliases. See Teradata Vantage™ - SQL Fundamentals, B035-1141.

Columns in a secondary index cannot have any of the following data types: .

• Period
• Geospatial
• BLOB
• CLOB
• ARRAY/VARRAY

In the index_column_name list, you cannot specify the system-derived columns PARTITION or PARTITION#Ln, where n is an integer from 1 through 62. However, you can specify a user-defined column named PARTITION or PARTITION#L n.

ORDER BY

Row ordering on each AMP by a single NUSI column. The ordering of the NUSI column values can either value-ordered or hash-ordered. Each multicolumn NUSI defined with an ORDER BY clause counts as two consecutive indexes against the limit of 32 per join index. You cannot specify an ORDER BY clause in the same join index definition as a partitioned primary index.

Following are the rules for specifying an ORDER BY clause:

• If you specify ORDER BY VALUES, column_name must be numeric with four bytes or less.

  ORDER BY VALUES is not supported for UDT columns.

• If you specify ORDER BY without specifying the HASH or VALUES keywords, VALUES is assumed by default.
• The ORDER BY column_name must be one of the columns specified in the select list.
• You cannot specify the system-derived PARTITION column as the ORDER BY column.

You can specify a user-defined column named partition.

VALUES

Value-ordering for the ORDER BY column. This is the default specification.

ORDER BY VALUES is not supported for UDT columns.

Select VALUES to optimize queries that return a contiguous range of values, especially for a covered index or a nested join.

HASH

Hash-ordering for the ORDER BY column.

You can specify ORDER BY HASH on a UDT column.

Select HASH to limit hash-ordering to one column, rather than all columns of the primary index, which is the default.

Hash-ordering a multicolumn NUSI on one of its columns allows the index to participate in a nested join where join conditions involve only that ordering column.

order_column_name

A column in the select list that specifies the sort order to be used for NUSI ordering.

Columns in a join index cannot have any of the following data types: Geospatial, BLOB, CLOB, XML, LOB UDT, XML UDT, VARIANT_TYPE, or ARRAY/VARRAY.

You cannot specify the system-derived columns PARTITION or PARTITION#Ln , where n is an integer ranging from 1 through 62, inclusive, as part of the column_name_2 list.

However, you can specify a user-defined column named PARTITION or PARTITION#L n.

Supported data types for a value-ordered, four-bytes-or-less column_name_2 are:

• BYTEINT
• DATE
• DECIMAL
• INTEGER
• SMALLINT