Periodically evaluate candidate indexes to make sure the Optimizer is using the indexes to help satisfy queries more quickly with acceptable overhead. Choose candidate PIs and PAs to provide even row distribution. Teradata provides tools to help you evaluate indexes.
Hash Functions to Evaluate PI and PA Candidates
Teradata includes system functions to help you determine whether candidate columns for PIs and PAs cause an even, nonskewed data distribution for the table rows. The following functions are described in more detail in Hash-Related Functions .
| Function | Description |
|---|---|
| HASHAMP | Returns the AMP number that corresponds to a given hash bucket number. |
| HASHBUCKET | Returns the hash bucket number that corresponds to a given row hash value. |
| HASHROW | Returns the row hash value for an expression or sequence of expressions. |
Together, these functions show how a set of candidate PI or PA columns distribute the rows of a table among the AMPs in a map.
Example: Row Distribution of the Employee Table
The following query shows the row distribution of the employee table based on a proposed unique primary index consisting of the single emp_no column. The resulting table shows whether the table rows are to be distributed evenly among the table AMPs using this primary index.
SELECT HASHAMP(HASHBUCKET(HASHROW(emp_no))), COUNT(*) FROM employee GROUP BY 1 ORDER BY 1;
HASHAMP(HASHBUCKET(HASHROW(emp_no))) Count(*)
------------------------------------------------- -----------
0 561
1 553
2 550
3 545
This is a relatively even distribution among these AMPs, so the emp_no column is a good unique primary index for this table.
EXPLAIN Request Modifier
You can use the EXPLAIN request modifier with a query to see a textual description of the steps the Optimizer uses to process the query, and determine if your indexes are being used as you intended. For more information about the EXPLAIN request modifier and how to interpret EXPLAIN output, see EXPLAIN Request Modifier and Interpreting EXPLAIN Output .
Semantically equivalent queries specified using different syntax can cause the Optimizer to generate different steps to get the same results. The different paths can have different costs in CPU time. EXPLAIN can help you choose the most efficient form of a query and make sure indexes are used.
Other Tools for Evaluating Indexes
The Teradata Index Analyzer tool analyzes index use and can recommend potentially useful secondary indexes. This tool uses the Query Capture Facility (QCF), which captures query activity and, optionally, the Optimizer query execution path steps logged to the Database Query Log (DBQL). Additionally, DBQL logs can be queried directly to determine whether specific indexes are being used in query processing. For more information about DBQL, see Tracking Query Behavior with Database Query Logging: Operational DBAs.