What is SQL query processing

  • Comment phase When the database server receives a query, it uses a parser to parse the statement and convert it to an algebraic representation, also known as a parse tree. At this stage comes the Parse tree for semantic and syntactic checks (e.g. the confirmation that the objects referenced in the query are available in the catalog) as well as for authorization checks, transformations of KEY JOINs and NATURAL JOINs using defined, referential integrity rules and the expansion of non-materialized views. The output of this phase is a rewritten query in the form of a parse tree that contains the comments (extended information such as data types) for all objects referenced in the original query.

  • Semantic transformation phase In this phase the query goes through iterative semantic transformations. While the query is still represented as an annotated parse tree, rewrite optimizations such as join eliminations, DISTINCT eliminations and predicate normalization are applied in this phase. The semantic transformations in this phase are carried out according to the semantic transformation rules that are heuristically applied to the representation of the parse tree. For more information, see Semantic Query Transformation.

    Queries with plans already cached by the database server skip this phase of query processing. Simple statements can also skip this phase of query processing. Many instructions that use a heuristic plan selection in the bypass mechanism of the optimizer are not processed by the semantic transformation phase. The complexity of the SQL statement determines whether this phase is applied to a statement. For more information, see Plan caching and whether to skip the query processing phase.

  • Optimization phase In the optimization phase, another internal representation of the query - the query optimization structure - is used, which is created from the parse tree. For more information, see How the Optimizer Works.

    Queries with plans already cached by the database server skip this phase of query processing. In addition, simple instructions can also skip this phase of query processing. For more information, see Plan caching and whether to skip the query processing phase.

    This phase is divided into two sub-phases:

    • Pre-optimization phase In the preliminary phase of optimization, the optimization structure is completed by the information that is required later in the enumeration phase. In this phase, the query is analyzed to find all relevant indexes and materialized views that can be used in the query's access plan. For example, at this stage the view matching algorithm determines all of the materialized views that can be used to satisfy all or part of a query. In addition, based on the predicate analysis of the query, the optimizer creates alternative join methods that can be used in the enumeration phase to create a join for the tables in the query. No decision is made at this stage about the best access plan for the query. The aim of this phase is to prepare for the enumeration phase.

    • Enumeration phase In this phase the optimizer enumerates possible access plans for the query and uses the modules that were generated in the previous phase of the optimization. The search area is very large and the optimizer uses a proprietary enumeration algorithm to generate and shorten the access plans. A cost estimate is calculated for each plan in order to compare the current plan with the best plan found so far. In these comparisons, expensive plans are discarded. The cost estimate takes into account resource usage such as disk and memory operations, the estimated number of rows for the intermediate results, the optimization target, the cache size, and so on. The result of the enumeration phase is the best access plan for the query.

  • Plan creation phase The plan build phase uses the best access plan to build the appropriate final representation of the query execution plan to run the query. You can view a graphical version of the plan in the plan viewer in Interactive SQL. The graphical plan has a tree structure in which each node is a physical operator that implements a specific relational, algebraic operation. "Hash-Join" and "Ordered-Group-By" are, for example, physical operators that implement a join or a group-by process. For more information, see Reading graphic plans.

    Queries with plans already cached by the database server skip this phase of query processing. For more information, see Plan caching and whether to skip the query processing phase.

  • Execution phase The result of the query is calculated by the query execution plan that was created in the plan creation phase.