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.
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:
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.
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;
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;
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. 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. |