WITH INDEX TYPE or WITH NO INDEX TYPE - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

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-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™
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 MultiLoad, which cannot 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 Teradata FastLoad for Teradata 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 more information, see INSERT/INSERT ... SELECT, MERGE, and CREATE ERROR TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.