Vantage Optimizer Process | SQL Request and Transaction Processing | Vantage - Vantage Optimizer Processes - Advanced SQL Engine - Teradata Database

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.

Query Optimization Processes

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 Parameterized Requests) other than to note that it does make that determination.

The input to the Optimizer is the Query Rewrite ResTree´ (see Query Rewrite, Statistics, and Optimization). The Optimizer then produces the optimized white tree, which it passes to an Optimizer subcomponent called the Generator (see Generator).

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 some independent components such as noncorrelated scalar subqueries, nonfolded derived tables or views, 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 are planned and executed incrementally. See Incremental Planning and Execution 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.
   1. Processes correlated subqueries by converting them to unnested SELECTs or simple joins.
   2. Processes noncorrelated scalar subqueries by materializing the subquery and placing its value in the USING row for the query.
   3. Searches for a relevant join or hash index.
   4. Materializes subqueries to spools.
   5. Analyzes the materialized subqueries for optimization possibilities in the following stages:
      1. Separates conditions from one another (see Predicate Marshaling).
      2. Pushes down predicates (see Predicate Pushdown and Pullup).
      3. Generates connection information.
      4. Locates any complex joins.
      5. Discovers aggregations and opportunities for partial group by optimizations.
   6. Generates size and content estimates of spools required for further processing (see Optimizer Use of Statistical Profiles).
   7. Generates an optimal single-table access path.
   8. Simplifies and optimizes any complex joins identified in stage 3.e.iv.
   9. 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 noncorrelated subquery EXIST predicate that connects with any outer table
   10. 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.
   11. Performs row and column partition elimination for partitioned tables.
   12. Uses a recursive greedy 1-table lookahead algorithm to generate the best join plan (see Determining the Order of Joins).
   13. 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 Determining the Order of Joins).
   14. Selects the better join plan of the 2 plans generated in stages 3.m and 3.n.
   15. If the join plan identified is heuristically determined to be adequate, generates a star join plan (see Star and Snowflake Join Optimization).
   16. 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.
7. Passes the optimized white tree to the Generator.

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