15.10 - Data Parcel Peeking and INSERT EXPLAIN and DUMP EXPLAIN Outcomes - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

When a data parcel is submitted with an INSERT EXPLAIN or DUMP EXPLAIN request, the plan might be generated with peeked USING and CURRENT_DATE, DATE, CURRENT_TIMESTAMP, or USER values, or both (see “Peeking at Parameterized Values in the Data Parcel” on page 28). If any of these values are peeked, then the query plan shows them.

If no data parcel is submitted with an INSERT EXPLAIN or DUMP EXPLAIN request, the resulting plan is generated without peeking at USING, CURRENT_DATE, DATE, CURRENT_TIMESTAMP, or USER values, so it is a generic plan by definition. Note that the Visual Explain, Teradata System Emulation Tool, and Teradata Index Wizard tools do not accept USING data as input while capturing query plans using INSERT EXPLAIN or DUMP EXPLAIN requests.

The Teradata Index Wizard and Teradata Viewpoint Stats Manager internally generate plans for workload queries in order to estimate workload costs, which are then used to determine optimal index recommendations and statistics that should be collected. When queries in the workloads specify USING request modifiers, the plan is generated without peeking at USING, CURRENT_DATE, DATE, CURRENT_TIMESTAMP, or USER values. Because of these factors, Request Cache peeking has no impact on the resulting index or statistics recommendations. Given that workload analyses should be independent of USING values, this behavior is correct.