Teradata follows these guidelines for index access:
Reference from Teradata orange book
Secondary indexes come with pros and cons:
Pros:
- Queries execute quickly when the index is accessed
- Ordered indexes allow fast range scans
- Hashed indexes are very useful when joining to the non-PI column
Cons:
- Whenever data is inserted, updated, or deleted from the base table, the index must also be maintained, thus affecting ETL time
- Sometimes it is difficult to force the optimizer to use the secondary index
Smaller tactical queries, where the predicate for the indexed column is used in the query, are proven to be effective and improve ...
