17.05 - Using the WITH INDEX TYPE or WITH NO INDEX TYPE Option - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

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

You can use this option to either include or exclude various types of secondary and join indexes from consideration for the analysis to be performed on the QCD data by the Teradata Index Wizard.

You can include for consideration, or exclude from consideration, by the analysis any of the following types of indexes.
  • Unique secondary (USI)
  • Nonunique secondary (NUSI)
  • Hash-ordered secondary
  • Value-ordered secondary
  • Single-table simple join
  • Single-table aggregate join

The default, indicated by not specifying this clause in your INITIATE INDEX ANALYSIS request, is to include all valid index types for consideration by the analysis. Note that the list of valid index types for index analysis does not include multitable join indexes or hash indexes.

For example, you might join indexes from consideration by the analysis, which you could consider to be undesirable for workloads that invoke utilities such as Teradata Archive/Recovery or MultiLoad, neither of which can be run against tables that have join indexes defined on them. For more information about restrictions and interactions with tables on which join indexes are defined, see CREATE JOIN INDEX in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - Database Design, B035-1094.

In this case, you can substitute FastLoad for MultiLoad to batch load rows into an empty staging table and then use either an INSERT … SELECT request with error logging or a MERGE request with error logging to move the rows into their target table if the scenario permits. For details, see INSERT/INSERT … SELECT, MERGE, and CREATE ERROR TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.