15.10 - Teradata Database Optimizer Processes - 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

This topic provides a survey of the stages of query optimization undertaken by the Optimizer. The information is provided only to help you to understand what sorts of things the Optimizer does and the relative order in which it performs them.

The following processes list the logical sequence of the processes undertaken by the Optimizer as it optimizes a DML request. The processes that are listed here do not include the influence of parameterized value peeking to determine whether the Optimizer should generate a specific plan or a generic plan for a given request (see “Peeking at Parameterized Values in the Data Parcel” on page 28) other than to note that it does make that determination.

The input to the Optimizer is the Query Rewrite ResTree´ (see “Query Rewrite” on page 72). The Optimizer then produces the optimized white tree, which it passes to an Optimizer subcomponent called the Generator (see “Generator” on page 65).

The Optimizer generates a static plan for all of the requests it processes by computing the cost of all the possible plan variations and selecting the plan with the lowest cost, and often chooses to use a static plan for a request. During the process of generating a static plan, the Optimizer assumes that all of the compilation-time demographic information it has is accurate and generates the plan for the entire request. However, this assumption is not always true, particularly for complex queries, where even all the advanced demographic estimation methods used by Teradata Database such as derived statistics and enhanced costing formulas can generate inaccurate cardinality, CPU usage, and I/O counts for the intermediate steps of a plan that lead to poor static plans.

If a request happens to contain a set of uncorrelated scalar subqueries, a set of single‑row table accesses, remote table operators, or some combination of these, the Optimizer can fragment the request into smaller components called request fragments. The demographics and inferred data from the execution of the plan fragments are used to plan subsequent request fragments. The request fragments, which isolate the uncorrelated scalar subqueries, single‑row table accesses, and remote table operators are planned and executed incrementally. See “Incremental Planning and Execution” on page 126 for more information about this process.

The Optimizer first generates a static plan for a request and then determines whether to execute the static plan or to generate and execute a dynamic plan. Information gathered while generating the static plan is used as part of this determination.

The Optimizer either sends the complete static plan or the summary information from the static plan as part of the first dynamic plan fragment to the dispatcher to apply workload filters, throttles, and classification criteria for the request. This behavior is controlled by an internal DBS Control field. Contact Teradata Customer Support if this field needs to be changed.

If the request passes the filters and throttles, the Optimizer continues processing the request with incremental planning and execution (IPE). The system does not apply the workload filters, throttles, and classification criteria to a plan fragment of a dynamic plan. Instead, the dynamic plan is executed using the workload definition that was determined for the request based on the static plan. Workload exceptions based on accumulated runtime metrics are applied during the execution of plan fragments of a dynamic plan.

The system employs the following process stages to optimize a request.

1 Receives the Query Rewrite ResTree´ as input.

2 If the request has been processed previously, determines whether to generate a specific plan or a generic plan for it.

3 Generates a static plan for the request in the following stages.

a Processes correlated subqueries by converting them to unnested SELECTs or simple joins.

b Processes uncorrelated scalar subqueries by materializing the subquery and placing its value in the USING row for the query regardless of whether the subquery is on the LHS or the RHS of the operator in the predicate.

c Searches for a relevant join or hash index.

d Materializes subqueries to spools.

e Analyzes the materialized subqueries for optimization possibilities in the following stages.

i Separates conditions from one another (see “Predicate Marshaling” on page 114).

i Separates conditions from one another (see “Predicate Marshaling” on page 114).

ii Pushes down predicates (see “Predicate Pushdown and Pullup” on page 104).

iii Generates connection information.

iv Locates any complex joins.

v Discovers aggregations and opportunities for partial group by optimizations.

f Generates size and content estimates of spools required for further processing (see “Optimizer Use of Statistical Profiles” on page 195).

g Generates an optimal single‑table access path.

h Simplifies and optimizes any complex joins identified in stage 3.e.iv.

i Maps join columns from a join (spool) relation to the list of field IDs from the input base tables to prepare the relation for join planning.

j Generates information about local connections. A connecting condition is one that connects an outer query and a subquery. A direct connection exists between 2 tables if either of the following conditions is found.

  • ANDed bind term: miscellaneous terms such as inequalities, ANDs, and ORs; cross, outer, or minus join term that satisfies the dependent information between the 2 tables
  • A spool of an uncorrelated subquery EXIST predicate that connects with any outer table
  • k Generates information about indexes that might be used in join planning, including the primary indexes for the relevant tables and pointers to the table descriptors of any other useful indexes.

    l Performs row and column partition elimination for partitioned tables.

    m Uses a recursive greedy 1-table lookahead algorithm to generate the best join plan (see “One-Join Lookahead Processing of n-Way Joins” on page 380).

    n If the join plan identified in stage 3.m does not meet the heuristics-based criteria for an adequate join plan, generate another best join plan using an n‑table lookahead algorithm (see “Five-Join Lookahead Processing of n-Way Joins” on page 383).

    o Selects the better join plan of the 2 plans generated in stages 3.m and 3.n.

    p Generates a star join plan (see “Star and Snowflake Join Optimization” on page 486).

    q Selects the better plan of the selection in stage o and the star join plan generated in stage 3.p.

    4 Determines whether to execute the static plan or to use IPE to generate a dynamic plan.

     

    IF the Optimizer decides to …

    THEN it continues with this process stage …

    use the static plan

    7

    generate a dynamic plan

    6

    5 Sends either the static plan or the summary of the static plan to the Dispatcher to apply the workload filters, throttles, and classification criteria for the request. If the request passes the filters and throttles, the Optimizer continues to evaluate the IPE processing.

    6 Generates a dynamic plan for the request under the IPE framework based on the static plan and using the following process stages.

    a Breaks the request into request fragments.

    b Identifies and executes any uncorrelated scalar subqueries and inserts the results into the main query.

    c Identifies and retrieves any single rows from tables that can be accessed by a UPI or USI and insert the results into the main query.

    d Identifies, executes and retrieves statistics feedback information from table operators and functions. Uses the statistics feedback to optimize remainder of the query.

    The Optimizer does not apply the workload filters, throttles, and classification criteria for the request to the plan fragments of a dynamic plan. Instead, it uses the workload definition that was determined for the request based on the static plan.

    7 Passes the optimized white tree to the Generator.

    The Generator (see “Generator” on page 65) then generates plastic steps for the plan chosen in stage 3.q or stage 6.