SQL Engine - The Query Transformer

Oracle Database Sql Processing

About

Query Optimizer Process

The query transformer is a component of the query optimizer and take as input a parsed query, which is represented by a set of query blocks.

The query blocks are nested or interrelated to each other. The form of the query determines how the query blocks are interrelated to each other.

The main objective of the query transformer is to determine if it is advantageous to change the form of the query so that it enables generation of a better query plan.

Several different query transformation techniques are employed by the query transformer, including:

Techniques

View Merging

Each view referenced in a query is expanded by the parser into a separate query block. The query block essentially represents the view definition, and therefore the result of a view. One option for the optimizer is to analyze the view query block separately and generate a view subplan. The optimizer then processes the rest of the query by using the view subplan in the generation of an overall query plan. This technique usually leads to a suboptimal query plan, because the view is optimized separately from rest of the query.

The query transformer then removes the potentially suboptimal plan by merging the view query block into the query block that contains the view. Most types of views are merged. When a view is merged, the query block representing the view is merged into the containing query block. Generating a subplan is no longer necessary, because the view query block is eliminated.

Oracle:
Grant the MERGE ANY VIEW privilege to a user to enable the optimizer to use view merging for any query issued by the user. Grant the MERGE VIEW privilege to a user on specific views to enable the optimizer to use view merging for queries on these views. These privileges are required only under certain conditions, such as when a view is not merged because the security checks fail.

See also :

  • MERGE ANY VIEW and MERGE VIEW privileges
  • OPTIMIZER_SECURE_VIEW_MERGING initialization parameter

Predicate Pushing

For those views that are not merged, the query transformer can push the relevant predicates from the containing query block into the view query block. This technique improves the subplan of the non-merged view, because the pushed-in predicates can be used either to access indexes or to act as filters.

Often the performance of queries that contain subqueries can be improved by unnesting the subqueries and converting them into joins. Most subqueries are unnested by the query transformer. For those subqueries that are not unnested, separate subplans are generated. To improve execution speed of the overall query plan, the subplans are ordered in an efficient manner.

Subquery Unnesting

Often the performance of queries that contain subqueries can be improved by unnesting the subqueries and converting them into joins. Most subqueries are unnested by the query transformer.

Example :

select t.*, (select c1 from t2 where t2.key1 = t.key1) c1,
            (select c2 from t3 where t3.key1 = t.key2) c2
  from t

Is the same with a regular join transformation that :

select t.*, t2.c1, t3.c3
  from t, t2, t3
 where t.key1 = t2.key1 (+)
   and t.key2 = t3.key2 (+)

For those subqueries that are not unnested, separate subplans are generated. To improve execution speed of the overall query plan, the subplans are ordered in an efficient manner.

Query Rewrite with Materialized Views

See SQL Engine - Query Rewrite





Discover More
Query Optimizer Process
SQL Engine - Query Optimizer (Query Optimization)

in a SQL Engine. A SQL statement can be executed in many different ways, such as: full table scans, index scans, nested loops, hash joins. The query optimizer determines the most efficient...
Rewrite
SQL Engine - Query Rewrite

query rewrite is a query performance technique enabled by the query transformer that rewrites the original query to use materialized view instead. As the materialized view (a view stored in a table fashion)...



Share this page:
Follow us:
Task Runner