IPE ( INCREMENTAL PLANNING EXECUTION )

Share via:
IPE ( INCREMENTAL PLANNING EXECUTION )

In this article, we will see the IPE ( INCREMENTAL PLANNING EXECUTION )

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.

About Incremental Planning and Execution

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
  • Non‑valid 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.

IPE Scenarios and Query Rewrite Possibilities

The request fragments currently available for IPE are restricted to uncorrelated scalar subqueries and single‑row accesses made possible by query conditions based on UPI or USI specifications, and the plans generated as a result of IPE are called dynamic plans in contrast to the traditional static plans.

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

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.

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

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.

Pushing the derived predicate into a derived table.

Using transitive closure to derive predicates inside the derived table.

Reserved Query Bands for IPE

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.

Querybands

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.

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTExperts Facebook
Linkedin Page : KT EXPERTS Linkedin

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment