Usage Notes - 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

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.

DDL Statements

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 …
ALTER TABLE … MODIFY PRIMARY INDEX adding a row-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 row-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

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)”); however, you cannot collect statistics on the system-derived PARTITION or PARTITION#L n 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)”).

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”).

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: Validate Index.

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”). 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.