16.20 - ALL - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

A NUSI should maintain row ID pointers for each logical row of a join index, not just the compressed physical rows.

ALL enables a NUSI to cover a join index, enhancing performance by eliminating the need to access the join index itself when all columns included in a query are contained in the NUSI. Use ALL when a NUSI is being defined for a join index and you want to make it eligible for the Optimizer to select when covering reduces access plan cost.

The ALL option ignores the case specification for a column so that a NUSI defined with the ALL option can:

  • Include case-specific values.
  • Cover a table or join index on a NOT CASESPECIFIC column set.

You cannot specify the ALL option for primary or unique secondary indexes.

Specifying ALL may require additional index storage space.

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

ALL is not applicable to geospatial indexes.