17.05 - ALL - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

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.