Teradata IPE
Teradata traditionally generates execution plans using information available during query optimization. This information includes statistics and cost factors, such as storage devices, CPU models, and available memory.
The optimizer generates multiple execution plans and selects the one with the lowest cost.
Teradata 14.10 introduces Incremental Planning and Execution (IPE), which utilizes available information at query optimization and collects additional data during query execution.
IPE – Splitting the Execution Plan into Fragments
To gain greater statistical insight, the optimizer divides requests into “request fragments” rather than creating the entire execution plan simultaneously.
A “fragment execution plan” is generated for every request fragment, and the output of each plan fragment is passed on to the next one.
One query plan fragment may lead to a spool table where statistical information such as the number of rows can be inputted during execution of the subsequent plan fragment.
IPE is currently limited to:
- Uncorrelated scalar subqueries
- Single‑row accesses for UPI or USI
This is an example of an uncorrelated scalar subquery, which returns a single row and is not joined to another table:
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > (SELECT MIN(t03.Col1) FROM TheSubTable t03)
;
The optimizer can use IPE for the above statement by splitting the request into two request fragments:
(SELECT MIN(t03.Col1) FROM TheSubTable t03);
Fragment 2
SELECT * FROM
TheTable t01 INNER JOIN TheTable2 t02 ON t01.Key = t02.Key
WHERE
t01.Col1 > [Result of Fragment 1];[/su_panel]
Feedback from a prior request fragment can activate sophisticated optimization methods, such as partition elimination.
The method employed for uncorrelated scalar subqueries is analogous to “hard coding” a predicate value within our query, thus enabling the optimizer to implement static partition elimination.
The technique discussed here generates execution plans that are referred to as “dynamic”.
The optimizer employs IPE for intricate queries that are expected to take at least one minute to run. Parsing time amounts to less than 10% of the estimated run time. IPE does not supplant the conventional static execution plan. In fact, a static plan is initially generated even with IPE at hand. The data gleaned from the static plan informs the decision to use IPE.
IPE does not supersede the conventional fixed execution plan, as a static plan is initially developed despite the availability of IPE. Data obtained from the static plan is subsequently utilized to determine the applicability of IPE.
New query bands enable us to compel the optimizer to implement IPE for simple queries.
IPE Query Bands
Query bands can be utilized to either enable or disable IPE usage for eligible requests with estimated execution times under one minute.
– The system should determine eligibility (the default behavior):
SET QUERY_BAND = ‘DynamicPlan=SYSTEM;’ FOR SESSION;
– Bypasses the cost thresholds (one minute execution time). If IPE is available, Teradata will use it:
SET QUERY_BAND = ‘DynamicPlan=SYSTEMX;’ FOR SESSION;
– Turns off IPE for the session:
SET QUERY_BAND = ‘DynamicPlan=OFF;’ FOR SESSION;
How can we know if IPE is available?
Teradata has unveiled a novel selection for the EXPLAIN modifier- DYNAMIC EXPLAIN. This choice is necessary to discern whether the optimizer implements the static or dynamic (IPE) execution plan.
Teradata displays the dynamic plan when the query is eligible for IPE; otherwise, it shows the static plan.
It is not necessary to always use DYNAMIC EXPLAIN to verify the execution plan because Teradata must execute every fragment in order to display the plan.
Starting from Teradata 14.10, the EXPLAIN modifier offers visibility on IPE utilization.
- Eligible for IPE:This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request.
- Eligible for IPE but does not meet cost thresholds (run time < 1 minute):If the request is not eligible for IPE, the output of the EXPLAIN modifier will not mention it (i.e., the explained plan looks like in pre-Teradata 14.10 releases)This request is eligible for incremental planning and execution (IPE) but does not meet cost thresholds. The following is the static plan for the request.
IPE and Workload Management
TASM does not fully support IPE as it enforces workload rules solely on the static plan, with workload exceptions being dependent on the dynamic plan.
Teradata 15.00 enables TASM to distinguish between requests that utilize IPE and those that do not, allowing for a straightforward categorization based on this distinction.
