The join verb for the Oracle query transformation is pushed in

The join verb push (join predicate pushdown) is an optimization method for the optimizer to handle a target SQL with a view, which means that although the optimizer will execute the definition SQL statement of the view in that SQL as a separate unit, However, the optimizer pushes the connection conditions that were originally in the view and between the views into the defined SQL statement for that view so that the indexes on the related base tables on the view can be used to get out of the nested circular joins based on the index.

The connection predicate pushes the resulting nested loop connections that are not necessarily out of the way of a more efficient execution plan, because the view in the original target SQL is associated with the external query as the join predicate pushes in, and Oracle must execute the view's defined SQL statement as a separate processing unit , which means that for each record in the result set where the external query is located, the defined SQL statement for the above view is executed separately, so that once the cardinality of the result set that the outer query is in is larger, the index can be used even when the definition statement for the above view is executed. The execution efficiency of the entire SQL is also higher than the hash join or sort merge connection that does not make the join predicate push-in. Therefore, Oracle will consider the cost when making the join predicate push-in, and Oracle will push the target SQL only if the cost value of the equivalent rewrite SQL for the nested loop join after the join verb is pushed in is less than the cost value of the original SQL.

Whether Oracle can make a connection predicate pushes into the type of the target view, the type of connection between the view and the external query, and the connection method. So far, Oracle has only supported the push of join predicates for the following types of views.

• View defines a view in the SQL statement that contains the Union all/union

• View defines a view that contains distinct in a SQL statement

• View definition SQL statement with GROUP by view

• The type of connection between and outside the query is a view of outer joins

• The type of connection between and outside the query is an anti-connected view

• The type of connection between and outside the query is a semi-connected view

Look at an instance pushed by a join predicate, create a test table, a related index, a normal view, and a view with UNION ALL

[Email protected]>create table EMP1 as SELECT * from EMP; Table created. [Email protected]>create table EMP2 as SELECT * from EMP; Table created. [Email protected]>create index IDX_EMP1 on EMP1 (empno); index created. [Email protected]>create index IDX_EMP2 on EMP2 (empno); index created. [Email protected]>create or replace view Emp_view as 2 Select Emp1.empno as Empno1 from EMP1; View created.  [Email protected]>create or replace view emp_view_union as 2 Select Emp1.empno as Empno1 from EMP1 3 UNION ALL 4 Select Emp2.empno as Empno1 from EMP2; View created.

Execute Test SQL

[Email protected]>select/*+ no_merge (emp_view) */Emp.empno 2 from Emp,emp_view 3 where Emp.empno=emp_view.empno1 (     +) 4 and emp.ename= ' FORD '; EMPNO----------7902

In the above SQL, we used the no_merge hint to make the view merge for Oracle not on the views Emp_view, thus having the basic condition to push the join predicate. Here the external query and view Emp_view join condition is "emp.empno=emp_view.empno1 (+)", because an index empno has been created on the column idx_emp1 of the base table EMP1 of the View Emp_view, And here is the connection type is the outer connection, according to the previous introduction, for the View Emp_view, all can make the connection predicate push into the conditions are already available, Oracle in the above SQL will consider to do the connection verb push-in. If you push the join predicate, the execution plan will walk the nested loop outer joins and access the base table EMP1 of the view Emp_view using the index IDX_EMP1 on the column empno.

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/8E/8D/wKiom1jFCEDhvanbAABNzKuYWHU391.png "title=" 1.png "alt=" Wkiom1jfcedhvanbaabnzkuywhu391.png "/>

As you can see from the execution plan, Oracle does go through nested, out-of-loop joins when it executes the test SQL, and uses the index IDX_EMP1 when accessing the base table EMP1 of the view Emp_view. And the value of the Name column on the id=3 step is "Emp_view", and the value of the Operation column is "VIEW pushed predicate". This means that Oracle does not do a view merge on Emp_view, but instead treats it as a separate execution unit and emp_view the connection condition between the external query and the view, "Emp.empno=emp_view.empno1 (+)" Pushed into the definition statement inside the view.

If you do not push the join verb, Oracle can only do a full table scan when accessing the base table EMP1 of the view Emp_view. Add no_push_pred hint in the test SQL (let the optimizer do not push the view emp_view as a connection verb) to execute again

[Email protected]>select/*+ no_merge (Emp_view) no_push_pred (emp_view) */Emp.empno 2 from Emp,emp_view 3 where EMP     . Empno=emp_view.empno1 (+) 4 and emp.ename= ' FORD '; EMPNO----------7902

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8E/8C/wKioL1jFCy2ioDq5AABDWHmpFFg163.png "title=" 1.png The "alt=" wkiol1jfcy2iodq5aabdwhmpffg163.png "/> Execution plan has become a hash JOIN OUTER, and the base table Emp_view for EMP1 does use a full table scan.

Now change the test SQL, replace the Emp_view with the Emp_view_union view, and change the connection type to inside connection and execute again

[Email protected]>select emp.empno 2 from Emp,emp_view_union 3 where Emp.empno=emp_view_union.empno1 4 and Emp.en     Ame= ' FORD '; EMPNO----------7902 7902

The definition SQL statement for View Emp_view_union contains union all, which itself cannot be a view merge, and thus has the basic condition of pushing the join predicate. The connection condition for the external query and view emp_view_union here is that the Emp.empno=emp_view_union.empno1 view has an index on the empno column on the base table, although the connection type here is an inner join, but for the containing union All of the view emp_view_union, all the conditions that can be pushed into the join predicate are already in place, meaning that Oracle executes the above SQL as a connection predicate push-in. If you push the join verb, the execution plan will go through nested loops, and the base table accessing the view will use the index on the empno above.

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M01/8E/8D/wKiom1jFDRnwBei4AABPpd24qCs026.png "title=" 1.png "alt=" Wkiom1jfdrnwbei4aabppd24qcs026.png "/> As you can see from the execution plan, Oracle's execution plan is the same as expected.

Add no_push_pred hint in SQL (let the optimizer do not push the view emp_view do the connection verb) to execute again

[Email protected]>select/*+ no_push_pred (emp_view_union) */emp.empno 2 from Emp,emp_view_union 3 where emp.empno=e     Mp_view_union.empno1 4 and Emp.ename= ' FORD '; EMPNO----------7902 7902

650) this.width=650; "src=" Https://s4.51cto.com/wyfs02/M01/8E/8C/wKioL1jFDhqwjiz0AABQe02iOwQ174.png "title=" 1.png "alt=" Wkiol1jfdhqwjiz0aabqe02iowq174.png "/> From the execution plan, you can see that the base table of the view does a full table scan without using the join verb to push in.

As mentioned earlier, Oracle will consider costs when making a join predicate push-in, and Oracle will push the target SQL only if the cost value of the equivalent rewrite SQL that is pushed into the nested loop join after the join verb is less than the cost value of the original SQL.

Now, to verify that adding cardinality hint to the SQL above allows the CBO to assume that the cardinality of the result set of the perimeter query is 10,000, which will dramatically increase the cost of nesting the nested loops after the join predicate is pushed in. If Oracle pushes into the JOIN predicate it does take into account the cost, then Oracle will no longer choose to do the join verb push-in.

[Email protected]>select/*+ cardinality (EMP 10000) */emp.empno 2 from Emp,emp_view_union 3 where Emp.empno=emp_vie     W_union.empno1 4 and Emp.ename= ' FORD '; EMPNO----------7902 7902

650) this.width=650; "src=" Https://s2.51cto.com/wyfs02/M00/8E/8C/wKioL1jFECnwwmWMAABQOeTi_Sg264.png "title=" 1.png "alt=" Wkiol1jfecnwwmwmaabqoeti_sg264.png "/>

[Email protected]>select/*+ cardinality (EMP 10000) push_pred (emp_view_union) */emp.empno 2 from Emp,emp_view_union     3 where Emp.empno=emp_view_union.empno1 4 and emp.ename= ' FORD '; EMPNO----------7902 7902

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M00/8E/8E/wKiom1jFEDqz5GsmAABYll0j4ac217.png "title=" 2.png "alt=" Wkiom1jfedqz5gsmaabyll0j4ac217.png "/> from the above test can be seen using cardinality hint after Oracle did not choose to do the connection predicate push, at this time the cost is 10, using Push_ Pred forces the join verb to push in and sees a cost of 20008. This also verifies that Oracle before making a connection verb pushes the membership to consider the cost.

Here's an example of using an inline view with a connection type of outer joins:

[Email protected]>select/*+ no_merge (emp_view_inline) */Emp.empno 2 from EMP, (select Emp1.empno as Empno1 from EMP1     ) Emp_view_inline 3 where Emp.empno=emp_view_inline.empno1 (+) 4 and emp.ename= ' FORD '; EMPNO----------7902

650) this.width=650; "src=" Https://s3.51cto.com/wyfs02/M01/8E/8E/wKiom1jFEdeQOQt1AABJAsoD2BM714.png "title=" 1.png "alt=" Wkiom1jfedeqoqt1aabjasod2bm714.png "/> for the above SQL, all the conditions that can be pushed into the connection predicate have been available, from the execution plan can also be seen that Oracle has also made a connection predicate push-in.

Go back to the start of SQL, change the outer connection to an inner join, and add push_pred hint (let the optimizer push the connection verb on the view emp_view) and USE_NL hint

[Email protected]>select/*+ no_merge (Emp_view) use_nl (Emp_view) push_pred (emp_view) */Emp.empno 2 from Emp,emp_view     3 where Emp.empno=emp_view.empno1 4 and emp.ename= ' FORD '; EMPNO----------7902

650) this.width=650; "src=" Https://s1.51cto.com/wyfs02/M02/8E/8C/wKioL1jFEkSiCKs0AABB9k1Ta0g175.png "title=" 2.png "alt=" Wkiol1jfeksicks0aabb9k1ta0g175.png "/>

From the execution plan, Oracle does not push the join verb because it does not belong to the kind of case that the switch mentions to push the connection predicate, even if the hint is used.

Although Oracle can make a connection predicate push-in is not related to whether the target view can do a view merge, or whether it is an inline view, it is related to the type of the target view, the type of connection to the outer query, and the connection method. So far, the case where Oracle can make a connection predicate push is limited to the types mentioned at the beginning, and if not, even if it seems simple, Oracle won't do it.

Refer to Oracle-based SQL optimization

Official Document: http://docs.oracle.com/cd/E11882_01/server.112/e41573/optimops.htm#i55050

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1905643

The join verb for the Oracle query transformation is pushed in