Examples - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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:

  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 .

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:

  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;