Using EXPLAIN Request Modifiers to Determine the Usefulness of Indexes - Teradata Database

Using EXPLAIN Request Modifiers to Determine the Usefulness of Indexes

The selection of indexes to support a request is not under user control. Additionally, you cannot specify resource options for Teradata Database indexes, nor can you control index locking.

Teradata SQL data manipulation language statements do not provide a method for specifying indexes as hints or pragmas in their syntax. The only references made to indexes in the Teradata SQL dialect concern their definition, not their use. See SQL Request and Transaction Processing for more information about why Teradata SQL does not use hints.

The implications of this behavior include the following:

It is critically important to collect statistics regularly on all indexed columns, all frequently joined columns, and all columns frequently specified in query predicates to ensure that the Optimizer has access to current information on the demographics of the database so it can know how to best optimize any query or update made to the database. Maintaining fresh statistics is the only way to tune index requests in Teradata Database.

For additional information concerning collecting and maintaining accurate database statistics, see “COLLECT STATISTICS (Optimizer Form)” in SQL Data Definition Language. You can also use the Teradata Viewpoint Stats Manager portlet or see the Automated Statistics Management Teradata Orange Book, 541-0009628.

It is equally important to build your SQL queries and updates in such a way that you know their performance will be most optimal.

Apart from good logical database design, there are two ways to ensure that you are accessing your data in the most efficient manner possible.

Use the EXPLAIN request modifier or the Visual Explain tool (see “Comparing EXPLAIN Request Reports Graphically” on page 151) to test various candidate requests and to note which indexes are used by the Optimizer in their execution (if any) as well as examining the relative length of time required to complete the request.

Use the Teradata Index Wizard client utility to recommend and validate sets of secondary or single‑table nonsparse join indexes, or both, for a particular query workload. See Teradata Index Wizard User Guide for further information.

EXPLAIN request modifier output provides you with the following basic information.

The step-by-step access method the Optimizer would use to execute the specified request given the current set of table statistics it has to work with.

The relative and worst case time estimates to perform the specified request.

While you cannot rely on the reported statement execution time as an absolute, you can rely on it as a relative means for comparison with other candidate requests against the same tables with the same statistics defined.

Even within the bounds of a constant system configuration a plan can change significantly because of shifting data demographics. The only way to track these shifts and to ensure that the Optimizer is building the best possible plans is to collect statistics frequently on your index columns, frequently joined columns, and columns frequently specified as query predicates and then run new EXPLAIN requests to generate reports to allow you to determine if the new statistics affect whether your indexes are used or not.

Extensive information about how to use the EXPLAIN request modifier is contained in “EXPLAIN Request Modifier” in SQL Data Manipulation Language.

Information about using the Visual Explain utility is contained in Teradata Visual Explain User Guide.