Teradata IPE
Traditionally, Teradata creates execution plans based on the information available at query optimization time. The information mainly used are statistics and cost factors, such as the type of used storage devices, CPU models, available main memory, etc.
The optimizer creates several execution plans and chooses the one with the most negligible costs.
Incremental Planning and Execution (IPE), introduced with Teradata 14.10, Â goes a step further, relying on the information available at query optimization time and using additional information collected during the execution of a query.
IPE – Splitting the Execution Plan into Fragments
To get more statistical insight, the optimizer is not creating the whole execution plan for a request at once but breaks requests into so-called “request fragments”.
For each request fragment, a “fragment execution plan” is created. The result of a plan fragment is fed into the subsequent plan fragment.
For example, one plan fragment could result in a spool table. The number of rows, or other statistical information, can be fed into the following plan fragment during query execution.
Currently, IPE is only available for:
- Uncorrelated scalar subqueries
- Single‑row accesses for UPI or USI
Here is an example of an uncorrelated scalar subquery (i.e., a subquery that returns only one 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];
The feedback of a previous request fragment often animates advanced optimization techniques, such as the usage of partition elimination.
The technique used for uncorrelated, scalar subqueries is similar to “hard code” a predicate value in our query, allowing the optimizer to use static partition elimination.
Execution plans created with this technique are called “dynamic execution plans”.
The optimizer uses IPE for complex queries with an estimated run time of at least one minute. The parsing time is less than 10% of the estimated run time. IPE is not replacing the traditional static execution plan. Even with IPE available, a static plan is created at first. Information gathered from the static plan is then used to decide if IPE will be used.
IPE is not replacing the traditional static execution plan. Even with IPE available, a static plan is created at first. Information gathered from the static plan is then used to decide if IPE will be used.
Several newly introduced query bands allow us to force the optimizer to use IPE for non-complex queries.
IPE Query Bands
We can use these query bands to avoid or activate IPE usage if the request is eligible for IPE, but the estimated execution time is below 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 introduced a new option for the EXPLAIN modifier: DYNAMIC EXPLAIN. We have to use this option to see if the optimizer uses the static or dynamic (IPE) execution plan.
When we use this new option, Teradata will show the dynamic plan if the query is eligible for IPE, otherwise the static plan.
Important: You should not start to use always DYNAMIC EXPLAIN to check the execution plan, as Teradata has to execute each plan fragment to show the dynamic plan!
Since Teradata 14.10, the EXPLAIN modifier gives insight into IPE usage:
- 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
Currently, IPE is not fully supported by TASM. TASM applied workload rules consistently on the static plan. Only workload exceptions are based on the dynamic plan.
With Teradata 15.00, TASM can identify requests using IPE, and a high-level classification can be done based on this (IPE / non-IPE request).