You should evaluate candidate indexes to ensure they are being used by the Optimizer to help satisfy queries more quickly as intended with acceptable overhead. Candidate PIs and PAs should be chosen carefully so that they provide an even row distribution. After you have created indexes, they should be re-evaluated periodically. Teradata Database provides tools to help you evaluate your indexes.
Hash Functions to Evaluate PI and PA Candidates
| 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. |
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 allows you to see whether the table rows would 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 would make 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 would use 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 and Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
Note that semantically equivalent queries specified using different syntax can cause the Optimizer to generate different steps to get the same results, and the different paths can have different costs in CPU time. EXPLAIN can help you choose the most efficient form of a query, in addition to ensuring indexes are used.
The Teradata Visual Explain tool provides a graphical tool that can also help you test candidate indexes and requests to determine which indexes are used by the Optimizer.
Other Tools for Evaluating Indexes
The Teradata Index Wizard and Teradata Index Analyzer tools analyze index use and can recommend potentially useful secondary indexes. These tools use 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 these tools, QCF, and DBQL, see Teradata Vantage™ - Database Administration, B035-1093.