DML Statements
|
This statement … |
Permits you to test the effects of … |
|
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 537); 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 544). 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 577). |
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: Validate Index” on page 626.
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 577). 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.
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 row partitioning 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.
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 row partitioning 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 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: