17.10 - Determining the Usefulness of Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1141-171K
Language
English (United States)

The selection of indexes to support a query is not under user control. You cannot provide the Teradata query optimizer with pragmas or hints, nor can you specify resource options or control index locking.

The only references made to indexes in the SQL language concern their definition and not their use. Teradata SQL data manipulation language statements do not provide for any specification of indexes.

There are several implications of this behavior.
  • To ensure that the optimizer has access to current information about how to best optimize any query or update made to the database, you must use the COLLECT STATISTICS statement to collect statistics regularly on all indexed columns, all frequently joined columns, and all columns frequently specified in query predicates.
  • To ensure that your queries access your data in the most efficient manner possible, use the EXPLAIN request modifier to try out various candidate queries or updates and to note which indexes are used by the optimizer in their execution (if any) as well as to examine the relative cost of the operation.