DIAGNOSTIC “Validate Index”
Purpose
Enables or disables the index and partition validation mode for the session and validates the indexes and partitioning expressions proposed by the Teradata Index Wizard utility.
Alternatively, you can use the statement to evaluate user‑specified indexes and partitioning expressions.
Syntax
where:
|
Syntax element … |
Specifies that index validation is … |
|
NOT ON |
disabled for the session. |
|
ON |
enabled for the session. When validation mode is turned ON, you can issue any of the following statements to simulate the existence (or nonexistence) of indexes and partitioning expressions for validation without actually creating or altering them. |
|
FOR SESSION |
set at the session scope level. No other diagnostic scope levels are valid. |
ANSI Compliance
DIAGNOSTIC “validate index” is a Teradata extension to the ANSI SQL:2011 specification.
Required Privileges
None.
Invocation
Normally invoked using the Teradata Index Wizard utility.
Target Level Emulation Requirement
If you are performing index analysis on a non‑production system, you must have Target Level Emulation enabled to use the DIAGNOSTIC “validate index” statement. For more information about Target Level Emulation, see SQL Request and Transaction Processing.
Index Validation Workflow
DIAGNOSTIC “Validate Index” enables you to capture query plans and then to examine whether or not the cost of the resulting plan is improved by the availability of simulated indexes or partitioning expressions.
The index and partitioning expression recommendations produced by the Teradata Index Wizard are a set of SQL DDL requests drawn from the following list:
|
This statement … |
Permits you to test the effects of … |
|
DDL Statements |
|
|
ALTER TABLE … MODIFY PRIMARY INDEX |
adding a partitioned primary index to a base table without actually doing so. The types of PARTITION BY clauses that are supported in Validation mode are limited to those types that are recommended by the Index Wizard: those defined over a single column using a RANGE_N function. Any subsequent queries whose plans are captured in the session are optimized and costed as if the table were partitioned according to the specified PARTITION BY clause. Because this request is submitted in Index Validation mode, the usual restriction that the table being altered must be empty does not apply because you are not changing the definition of the actual data table. See “ALTER TABLE” in SQL Data Definition Language. |
|
CREATE INDEX |
adding a secondary index without actually doing so. See “CREATE INDEX” in SQL Data Definition Language. |
|
DROP INDEX |
dropping a secondary index without actually doing so. If you submit a DROP INDEX request, then the corresponding index definition is considered dropped until you reset the Index Validation mode to NOT ON FOR SESSION. See “DROP INDEX” in SQL Data Definition Language. |
|
CREATE JOIN INDEX |
adding a join index without actually doing so. See “CREATE JOIN INDEX” in SQL Data Definition Language. |
|
DML Statements |
|
|
COLLECT STATISTICS (QCD Form) |
collecting QCD statistics (but not Optimizer statistics). The system then uses the collected statistics for analysis of any requests you execute in validation mode. You can only use the QCD form of this statement in validation mode. If you submit the Optimizer form of COLLECT STATISTICS while you are in Index Validation mode, the system aborts the request and returns an error. You can issue COLLECT STATISTICS requests on a partitioning column using the COLUMN syntax (see “COLLECT STATISTICS (QCD Form)” on page 557); however, you cannot collect statistics on the system‑derived PARTITION or PARTITION#Ln columns. Because this request does not write definitions into the data dictionary, it is not treated as DDL when submitted for validation. |
|
DROP STATISTICS (QCD Form) |
dropping statistics without actually doing so. You must use the QCD form of this statement (see “DROP STATISTICS (QCD Form)” on page 564). If you submit the Optimizer form of DROP STATISTICS while you are in Validation mode, the system aborts the request and returns an error. Because this request does not drop definitions from the data dictionary, it is not treated as DDL when submitted for validation. |
|
INSERT EXPLAIN |
performing an INSERT EXPLAIN request without actually doing so. You cannot use DIAGNOSTIC “Validate Index” to submit INSERT EXPLAIN … WITH STATISTICS requests in Index Validation mode (see “INSERT EXPLAIN” on page 596). |
Note: These simulated definitions are private to the current session and are not written to the data dictionary. You must submit these SQL requests following the DIAGNOSTIC SQL request as shown in “Example 1” on page 645.
In Index Validation mode, the Parser creates pseudoindexes corresponding to the CREATE INDEX and CREATE JOIN INDEX requests in the workload cache and collects the statistics for the COLLECT STATISTICS request into the QCD tables. Similarly, the system creates pseudo-partitioning expressions corresponding to the ALTER TABLE … MODIFY PRIMARY INDEX requests in the workload cache.
After performing the pseudo-DDL or pseudo‑DML request recommendations with DIAGNOSTIC “Validate Index,” you must submit the workload SQL requests using an INSERT EXPLAIN request (see “INSERT EXPLAIN” on page 596). The existing pseudoindexes and partitioning expressions, along with their corresponding statistics, including those collected with the COLLECT STATISTICS (Optimizer Form) request, are used to generate the query plan. The indexes on which DROP INDEX is submitted are ignored during plan generation.
Because recommendations are made for the workload as a whole, you should submit the recommendation requests before you submit the SQL requests with the INSERT EXPLAIN modifier. You can then verify the performance improvement with the captured query plan.
After you are done validating SQL workload requests, you must reset the Index Validation mode for the session by submitting a DIAGNOSTIC “Validate Index” NOT ON FOR SESSION request.
Example
The following example assumes that the index analysis phase of the Teradata Index Wizard has proposed the following recommendations for the lineitem table:
CREATE INDEX lx_pk (l_partkey) ON lineitem;
COLLECT STATISTICS FOR SAMPLE 50 INTO Wizard_QCD ON lineitem INDEX lx_pk;
These DDL requests are saved in the IndexRecommendations table in the specified QCD.
You then submit the following sequence of SQL requests in the order indicated to validate the recommendation:
1 Turn Index Validation mode on:
DIAGNOSTIC "Validate Index" ON FOR SESSION;
2 Simulate a new NUSI for the table lx_pk and collect statistics on it:
CREATE INDEX lx_pk (l_partkey) ON lineitem;/*Does not update
the data dictionary
*/
COLLECT STATISTICS FOR SAMPLE 50
INTO Wizard_QCD
ON lineitem INDEX lx_pk;/*Does not update the data dictionary */
3 Capture the query plan with the simulated PPI for tab1 in place:
INSERT EXPLAIN
INTO myqcd
AS validate_qtab1;
4 Submit the query:
SELECT *
FROM lineitem
WHERE l_partkey >= 472;
5 Turn Index Validation mode off:
DIAGNOSTIC "Validate Index" NOT ON FOR SESSION;
6 Examine the step costs of the captured query plan:
SELECT stepkind, cost
FROM myqcd.querystepsview
WHERE cost > 0
AND queryid = (SELECT MAX(queryid)
FROM myqcd.query)
ORDER BY stepnum;
This procedure can also be used to perform what-if analyses on the requests.
The SQL interface allows you to simulate indexes and optionally use sampled statistics in order to gauge the behavior of SQL requests with the proposed indexes. Note that the index statistics used for this analysis are fetched from the QCD, not the data dictionary.
If the resulting plans show improvements, then you can create and drop the suggested indexes using the CREATE INDEX and DROP INDEX requests. See “CREATE INDEX” and “DROP INDEX” in SQL Data Definition Language.
You should then collect statistics on any newly defined indexes and partitioning columns using the COLLECT STATISTICS (Optimizer Form) and DROP STATISTICS (Optimizer Form) requests. For information about using these statements, see “COLLECT STATISTICS (Optimizer Form)” and “DROP STATISTICS (Optimizer Form)” in SQL Data Definition Language.
Example
The following example simulates a changed partitioning expression and then tests the effects of the new partitioning expression:
1 Turn Index Validation mode on:
DIAGNOSTIC "Validate Index" ON FOR SESSION;
2 Simulate a new partitioning expression for the table tab1:
ALTER TABLE tab1
MODIFY PRIMARY INDEX
PARTITION BY RANGE_N(c_date BETWEEN DATE' 1993-01-01'
AND DATE '2000-12-31'
EACH INTERVAL '1' MONTH);
3 Capture the query plan with the simulated PPI for tab1 in place by submitting an INSERT EXPLAIN request like the following example:
INSERT EXPLAIN
INTO myqcd
AS validate_q1
SELECT *
FROM tab1
WHERE c_date >= DATE;
4 Turn Index Validation mode off:
DIAGNOSTIC "Validate Index" NOT ON FOR SESSION;
5 Examine the step costs of the captured query plan:
SELECT stepkind, cost
FROM myqcd.querystepsview
WHERE cost > 0
AND queryid = (SELECT MAX(queryid)
FROM myqcd.query)
ORDER BY stepnum;
For More Information
For more information about the Teradata Index Wizard, see SQL Request and Transaction Processing.
For more information about how to use the client utility, see Teradata Index Wizard User Guide.
Also see the documentation for the following Index Validation‑related SQL statements: