Shulou(Shulou.com)06/01 Report--

Overview

The function of the Oracle query converter is to rewrite the original sql into statements with the same semantics in order to obtain a more efficient sql.

There are four main technologies of query transformation: subquery expansion, view merging, predicate push, and star transformation.

Understanding query transformation is the basis of mastering SQL optimization, this article will make some brief introduction to these four technologies.

Subquery expansion

Subquery expansion means that the optimizer no longer treats the subquery as a separate unit, but converts it into an equivalent join way. There are two ways to transform: one is to join; the result set of the subquery as a view, and the other is to split the table or view in the subquery and join it with the outer table or view. The following conditions can be expanded before a subquery:

Any (= any and in are equivalent)

All (all is equivalent to not in)

Exists

Not exists

Single row condition (where followed by =, =, etc.)

An example of subquery expansion:

The final converted statement:

You can see that the dept table in the subquery is removed and inner join with the emp table of the external query block. You can do this if the deptno column of the dept table is the only key. If the deptno column is not the only key, the semi join (so-called semi-join) will be done:

If you do not expand the subquery, the execution plan of the filter type will be taken, and the subquery will be executed in the last step to filter the result set after the emp full table scan:

Look at an example of join a subquery result set as an inline view and an outer query block:

The execution plan still leaves hash join semi. To make the transformation equivalent, you must first complete the join of departments and locations in the subquery, and the result set is used as the inline view VM_NSO_1 and join with the result set of the outer query block.

In the following execution plan, the result of the subquery is used as the driving table of nest loop. In order to ensure that the result set is correct, the subquery needs to be deduplicated by hash unique.

If the following conditions are met (but not limited to), the expansion of the subquery may result in non-equivalence of the transformation, so the expansion will not be made:

The connector after where is = all or any

The subquery after exists has rownum

Subqueries after exists have having clause, cube clause, or rollup clause

For example, the following example:

View merging

View merge refers to the transformation of a query that contains views so that the query contains only the base table. View merging provides more access paths and join possibilities. That is, an execution plan that does not do a view merge is included in an execution plan that does a view merge. The following example can help you understand this sentence:

If you do not do a view merge, the execution plan is as follows:

You can see that the view merge is not done by emp100. The word view appears in the execution plan, and the name column corresponds to the view name emp100.

The second execution plan has an extra line of view, and the access path and cost are the same as the first.

View merging cannot be done if the view definition contains the following:

Set operator (UNION,UNION ALL,INTERSECT,MINUS)

Connect by clause

Rownum pseudo column

These restrictions are made to prevent views from merging to get the wrong result set.

An example of not being able to view merging:

Complex view merging

Complex view merging technology allows you to expand views that contain gourp by or distinct.

The _ COMPLEX_VIEW_MERGING parameter controls whether complex view merging is activated, which defaults to true after 9i and is also controlled by the OPTIMIZER_FEATURES_ENABLE parameter:

Externally connected views merge

In sql with external connections, view merging needs to meet one of the following conditions:

The view is an externally connected driver table

The definition of a view contains only a single table

In the following example, view v1 contains two base tables. View merging occurs when you do an externally connected driver table, but not when you do a driven table.

Predicate push into

When dealing with views that cannot be merged, the optimizer can choose to push the predicates of the external query into the query block of the view, or pull the predicates in the view out to the main query. This earlier processing of the result set of the view may reduce the cost of the next steps.

An example of a predicate pushed inside a view:

Note the execution of the condition EMPLOYEE_ID in the plan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.