The selection of indexes to support a query is not under user control. You cannot provide the Teradata Database 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 or the Teradata Visual Explain client utility 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.
- Use the Teradata Index Wizard client utility to recommend and validate sets of secondary indexes, single-table join indexes, and PPIs for a given query workload.