Improving Query Optimization with Teradata IPE

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 fed into the 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:

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 complex 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 replace the conventional static execution plan. A static plan is initially generated even when IPE is available, and the data derived from that static plan informs the decision to use 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 introduced a new option for the EXPLAIN modifier: DYNAMIC EXPLAIN. This option is needed to determine whether the optimizer uses 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):

  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.

  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)

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.