15.10 - Incremental Planning and Execution - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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.

  • Inaccurate or unknown column correlations
  • Nonvalid 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.

    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:

  • uncorrelated scalar subqueries
  • single‑row accesses made possible by query conditions based on UPI or USI specifications
  • remote table operators
  • 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.

  • Using transitive closure to derive predicates.
  •      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;
  • Pushing the derived predicate into a derived table.
  •      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;
  • Using transitive closure to derive predicates inside the derived table.
  •      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.

  • 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.