INDEX - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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