15.10 - Incremental Planning and Execution - Teradata Database

The data that the Optimizer acquires from base statistics and demographics, derived statistics, cost formulas, entropy‑based algorithms, and other advanced techniques is referred to as static information, and plans that are produced using only this sort of information to inform the Optimizer about the demographics of the data are called static plans.

Experience has demonstrated that even the best static information can cause faulty optimization of complex queries at compilation time for the following reasons, among others.

Inaccurate or unknown column correlations

Nonvalid costing and selectivity assumptions

Inaccurate approximations using probability models

The incremental planning and execution (IPE) framework is one mechanism for optimizing complex SQL queries. The basic concept of IPE is to decompose complex requests into smaller elements, or request fragments, generate and execute a plan fragment for a request fragment, then to insert the results of the executed plan fragments back into subsequent request fragments or the main query.

The plans generated as a result of IPE are called dynamic plans in contrast to the traditional static plans.

The request fragments currently available for IPE are restricted to the following types:

uncorrelated scalar subqueries

single‑row accesses made possible by query conditions based on UPI or USI specifications

remote table operators

As an example of how IPE might process a request, consider the following example request that contains an uncorrelated scalar subquery.

     SELECT * 

     FROM t1, (SELECT t2.a2 

               FROM t2, t3 

               WHERE t2.a2 = t3.a3

               GROUP BY t2.a2) AS dt (a2)

     WHERE t1.a1 < (SELECT MAX(t4.a4) 

                    FROM t4)

     AND   t1.a1 = dt.a2;

The Optimizer detects the highlighted uncorrelated scalar subquery as an opportunity to improve performance using IPE and breaks the request down into request fragments that can be planned and executed incrementally, with the results of executing each corresponding plan fragment used in planning subsequent request fragments, as the following text demonstrates.

For the first request fragment, the system plans and executes all uncorrelated scalar subqueries first.

     SELECT MAX(t4.a4) INTO v1 

     FROM t4;

The system then substitutes the value for v1 into the WHERE clause of the second request fragment as follows.

     SELECT *

     FROM t1, (SELECT t2.a2

               FROM t2, t3

               WHERE t2.a2 = t3.a3

               GROUP BY t2.a2) AS dt (a2)

     WHERE t1.a1 < v1

     AND   t1.a1 = dt.a2;

The Optimizer first generates the plan fragment for request fragment 1 and then executes the plan fragment. This returns the result for v1. Assume for this example that v1 is returned with the value 20. The resulting value is substituted as a constant into request fragment 2.

This substitution enables the following query rewrite possibilities.

Using transitive closure to derive predicates.

     SELECT * 

     FROM t1, (SELECT t2.a2 

               FROM t2, t3 

               WHERE t2.a2 = t3.a3

               GROUP BY t2.a2) AS dt (a2)

     WHERE t1.a1 < 20

     AND   t1.a1 = dt.a2

     AND   dt.a2 < 20;

Pushing the derived predicate into a derived table.

     SELECT * 

     FROM t1, (SELECT t2.a2 

               FROM t2, t3 

               WHERE t2.a2 = t3.a3

               AND   t2.a2 < 20

               GROUP BY t2.a2) AS dt (a2)

     WHERE t1.a1 < 20

     AND   t1.a1 = dt.a2

     AND   dt.a2 < 20;

Using transitive closure to derive predicates inside the derived table.

     SELECT *

     FROM t1, (SELECT t2.a2

               FROM t2, t3

               WHERE t2.a2 = t3.a3

               AND   t2.a2 < 20

               AND   t3.a3 < 20

               GROUP BY t2.a2) AS dt (a2)

     WHERE t1.a1 < 20

     AND   t1.a1 = dt.a2

     AND   dt.a2 < 20;

Note: As a general rule, customer applications should not use reserved query band names. They are reserved for use by Teradata‑written applications and applications written by third party Teradata partners.

The following query bands are reserved for use by incremental planning and execution.

Query Band Name	Valid Values	Description
DynamicPlan	OFF	IPE is disabled. As a result, only a static plan can be generated.
	SYSTEM	This is the default value for DynamicPlan. When a specific plan is to be generated for a request, Teradata Database determines whether to use IPE to generate a dynamic plan or to generate only a static plan and not use IPE.
	SYSTEMX	When a specific plan is to be generated for a request, Teradata Database determines whether to use IPE to generate a dynamic plan or to generate only a static plan and not use IPE. Teradata Database ignores any system‑determined thresholds for this value.
SpecificPlan	ALWAYS	The Optimizer generates a specific plan and uses the DynamicPlan query band setting.
	OFF	The Optimizer generates a generic plan for parameterized requests and does not use IPE. The Optimizer generates a generic plan for nonparameterized requests, and does not use IPE.
	SYSTEM	This is the default value for SpecificPlan. If a request is parameterized, the Optimizer uses the parameterized request cache settings to determine whether a generic or specific plan is generated. For a specific plan, the Optimizer uses the DynamicPlan query band setting. For a generic plan, the Optimizer generates a static plan and does not use IPE. If a request is nonparameterized and being seen for the first time, the Optimizer uses the DynamicPlan query band setting. If a request is nonparameterized and being seen for the second time, the Optimizer generates a static plan, does not use IPE, and uses the parameterized request cache settings to determine whether to cache the plan or not.