Evaluating Indexes | Database Design | Teradata Vantage - 17.10 - Evaluating Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
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 provides tools to help you evaluate your indexes.

Hash Functions to Evaluate PI and PA Candidates

Teradata 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 Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
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 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.

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 Teradata Index Analyzer, QCF, and DBQL, see Teradata Vantage™ - Database Administration, B035-1093.