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:
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.
Apart from good logical database design, there are two ways to ensure that you are accessing your data in the most efficient manner possible.
EXPLAIN request modifier output provides you with the following basic information.
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 request modifiers 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.