Example: Validate Index
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:
- Turn Index Validation mode on:
DIAGNOSTIC "Validate Index" ON FOR SESSION;
- 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 */
- Capture the query plan with the simulated row partitioning for tab1 in place:
INSERT EXPLAIN INTO myqcd AS validate_qtab1;
- Submit the query:
SELECT * FROM lineitem WHERE l_partkey >= 472;
- Turn Index Validation mode off:
DIAGNOSTIC "Validate Index" NOT ON FOR SESSION;
- 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: Validate Index with a Changed Partitioning Expression
The following example simulates a changed partitioning expression and then tests the effects of the new partitioning expression:
- Turn Index Validation mode on:
DIAGNOSTIC "Validate Index" ON FOR SESSION;
- 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);
- 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;
- Turn Index Validation mode off:
DIAGNOSTIC "Validate Index" NOT ON FOR SESSION;
- 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;