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

The join predicate push (Join Predicate Pushdown) is an optimization method for the optimizer to deal with the target SQL with view, which means that although the optimizer executes the definition SQL statement of the view in the SQL as a separate unit, the optimizer pushes the join conditions that were originally in the external query of the view and between the view into the definition SQL statement of the view. This is in order to be able to use the index on the relevant base table within the view, and then get out of the index-based nested loop join.

The index-based nested loop join caused by the push of the join predicate does not necessarily lead to a more efficient execution plan, because when the join predicate is pushed, the view in the original target SQL is associated with the external query, and the Oracle must execute the definition SQL statement of the view as a separate processing unit, which means that for each record in the result set of the external query The definition SQL statement of the above view has to be executed separately, so that once the Cardinality of the result set of the external query is relatively large, even if the index can be used when executing the definition statement of the above view, the execution efficiency of the whole SQL is not necessarily higher than the hash join or sort merge join without the join predicate push. Therefore, the Oracle will consider the cost when doing the join predicate push. Only when the cost value of the equivalent rewrite SQL of the nested loop connection after the join predicate push is less than the cost value of the original SQL, will Oracle do the join predicate push to the target SQL.

Whether Oracle can do join predicate push depends on the type of target view, the type of join between that view and the external query, and the join method. So far, Oracle only supports join predicate push on the following types of views.

View defines the view that contains UNION ALL/UNION in the SQL statement

View defines the view that contains DISTINCT in the SQL statement

View defines the view that contains GROUP BY in the SQL statement

The connection type between and external query is an externally joined view

The connection type between and external query is an anti-join view

The join type between and the external query is a semi-joined view

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

Scott@TEST > create table emp1 as select * from emp;Table created.scott@TEST > create table emp2 as select * from emp;Table created.scott@TEST > create index idx_emp1 on emp1 (empno); Index created.scott@TEST > create index idx_emp2 on emp2 (empno); Index created.scott@TEST > create or replace view emp_view as 2 select emp1.empno as empno1 from emp1;View created.scott@TEST > 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 the test SQL

Scott@TEST > 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 no_merge hint to get Oracle to do view merging against the view EMP_VIEW, so that we have the basic conditions for doing join predicate push. Here, the join condition between the external query and the view EMP_VIEW is "emp.empno=emp_view.empno1 (+)". Since the index IDX_EMP1 has been created on the column EMPNO of the base table EMP1 of the view EMP_VIEW, and the join type here is an external join, according to the previous introduction, for the view EMP_VIEW, all the conditions that can be pushed by the join predicate are met, and Oracle will consider doing the join predicate push when executing the above SQL. If the join predicate push is made, the execution plan takes a nested out-of-loop join and the index IDX_EMP1 on column EMPNO is used when accessing the base table EMP1 of the view EMP_VIEW.

As you can see from the execution plan, Oracle did take a nested out-of-loop join when executing the test SQL, and the index IDX_EMP1 was used when accessing the base table EMP1 of the view EMP_VIEW. And the value of the Name column on the execution step of the Id=3 is "EMP_VIEW" and the value of the Operation column is "VIEW PUSHED PREDICATE". This shows that Oracle does not do view merge to the view EMP_VIEW, but executes it as a separate execution unit, and pushes the join condition "emp.empno=emp_view.empno1 (+)" between the external query and the view EMP_VIEW into the definition statement of the view.

Without the join predicate push, Oracle can only do a full table scan when accessing the base table EMP1 of the view EMP_VIEW. Add no_push_pred hint to the test SQL (tell the optimizer not to push the join predicate to the view EMP_VIEW) and execute it again

Scott@TEST > 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

The execution plan has become HASH JOIN OUTER, and it is true that the base table EMP1 of EMP_VIEW is a full table scan.

Now change the test SQL, replace EMP_VIEW with the EMP_VIEW_UNION view, and change the connection type to inner connection, and execute it again

Scott@TEST > select emp.empno 2 from emp,emp_view_union 3 where emp.empno=emp_view_union.empno1 4 and emp.ename='FORD'; EMPNO- 7902 7902

The definition SQL statement of view EMP_VIEW_UNION contains UNION ALL, which can not do view merge itself, so it has the basic condition of doing join predicate push. Here, the join condition of the external query and the view EMP_VIEW_UNION is that the "emp.empno=emp_view_union.empno1" view has an index to the EMPNO column on the base table. Although the join type here is an inner join, for the view EMP_VIEW_UNION that contains UNION ALL, all the conditions that can be pushed into the join predicate have been met, which means that when Oracle executes the above SQL, consider doing the join predicate push. If you push the join predicate, the execution plan will take a nested loop join, and the base table that accesses the view will use the index on the above EMPNO.

As can be seen from the implementation plan, Oracle went the same way as expected.

Add no_push_pred hint to the SQL (tell the optimizer not to push the join predicate to the view EMP_VIEW) and execute it again

Scott@TEST > select / * + no_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

As you can see from the execution plan, without using the join predicate push, a full table scan is performed on the base table of the view.

As mentioned earlier, Oracle will consider the cost when doing join predicate push. Only when the cost value of the equivalent rewrite SQL of the nested loop join after the join predicate push is less than the cost value of the original SQL, Oracle will push the join predicate to the target SQL.

Now let's verify that adding cardinality hint to the above SQL makes CBO think that the Cardinality of the result set of the peripheral query is 10,000, which will dramatically increase the cost of the nested loop join after the join predicate push. If Oracle does consider the cost when doing the join predicate push, then Oracle will no longer choose to do the join predicate push.

Scott@TEST > select / * + cardinality (emp 10000) * / emp.empno 2 from emp,emp_view_union 3 where emp.empno=emp_view_union.empno1 4 and emp.ename='FORD'; EMPNO- 7902 7902

Scott@TEST > 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

From the above test, we can see that Oracle did not choose to push the join predicate after using cardinality hint, and the cost is 10. Using push_pred to force the join predicate push, we can see that the cost is 20008. This also verifies that the cost will be considered when Oracle makes a join predicate push.

Let's look at an example where an embedded view is used and the connection type is an outer connection:

Scott@TEST > 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

For the above SQL, all the conditions for doing join predicate push are in place, and it can be seen from the execution plan that Oracle does do join predicate push.

Go back to the SQL executed at the beginning, change the outer join to the inner join, and add push_pred hint (let the optimizer push the join predicate to the view EMP_VIEW) and USE_NL hint

Scott@TEST > 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

In terms of the execution plan, Oracle does not do join predicate push because it does not belong to the cases mentioned by the switch that can do join predicate push, even if Hint is used.

Although whether Oracle can do join predicate push has nothing to do with whether the target view can do view merge or whether it is an embedded view, it has something to do with the type of the target view, the join type with the outer query and the join method. So far, the cases in which join predicates can be pushed in Oracle are limited to the types mentioned at the beginning, and if they are not, even if it looks simple, Oracle will not do it.

Refer to "SQL Optimization based on Oracle"

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

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.