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.
Implications of this behavior:
- To make sure 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 (Optimizer Form) statement to collect statistics regularly on all indexed columns, all frequently joined columns, and all columns frequently specified in query predicates.
- To make sure that your queries access your data in the most efficient manner possible, use the EXPLAIN request modifier to test candidate queries or updates and to note which indexes are used by the optimizer in their execution (if any) and to examine the relative cost of the operation.