16.10 - Evaluating Indexes - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

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

Teradata Database includes system functions that can help you evaluate whether candidate columns for PIs and PAs would result in an even, nonskewed data distribution for the table rows. The following functions are described in more detail in SQL Functions, Operators, Expressions, and Predicates.
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 can show how a given set of candidate PI or PA columns will 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 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 SQL Data Manipulation Language and SQL Request and Transaction Processing.

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 Database Administration.