Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to understand the join predicate push in SQL optimization. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The join predicate optimized by SQL pushes in:

Environmental preparation:

Create table emp1 as select * from emp

Create table emp2 as select * from emp

Create index idx_emp1 on emp1 (empno)

Create index idx_emp2 on emp2 (empno)

Create or replace view emp_view as select emp1.empno as empno1 from emp1

Create or replace view emp_view_union as select emp1.empno as empno1 from emp1 union all select emp2.empno as empno1 from emp2

Empowered, scott users can turn on set autot

Grant select on v_$sesstat to scott

Grant select on v_$statname to scott

Grant select on v_$mystat to scott

Sql example 1:

Select / * + no_merge (emp_view) * / emp.empno from emp,emp_view where emp.empno=emp_view.empno1 (+) and emp.ename='FROD'

You can see that the emp table and the emp_view view are joined to the left, and the view is a supplementary table.

View the execution plan:

SQL > set autot traceonly

SQL > set line 250

SQL > select / * + no_merge (emp_view) * / emp.empno from emp,emp_view where emp.empno=emp_view.empno1 (+) and emp.ename='FROD'

No rows selected

Execution Plan

Plan hash value: 101695337

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 12 | 4 (0) | 00:00:01 |

| | 1 | NESTED LOOPS OUTER | | 1 | 12 | 4 (0) | 00:00:01 |

| | * 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0) | 00:00:01 |

| | 3 | VIEW PUSHED PREDICATE | EMP_VIEW | 1 | 2 | 1 (0) | 00:00:01 |

| | * 4 | INDEX RANGE SCAN | IDX_EMP1 | 1 | 13 | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("EMP". "ENAME" = 'FROD')

4-access ("EMP1". "EMPNO" = "EMP". "EMPNO")

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

The execution plan is easy to understand: step 2 is at the same level as step 3, but step 2 does not have a child ID, so execute step 2 first.

Step 2: this step has a filter condition filter ("EMP". "ENAME" = 'FROD'). The whole table scans the emp table to find all the data of ename=frod.

Step 4: index range scan, and the target condition satisfies access ("EMP1". "EMPNO" = "EMP". "EMPNO"). Here, the condition of the left outer join of the view and the table is pushed into the view.

Step 3:VIEW PUSHED PREDICATE states that the view merging is not done and the view is executed as a separate unit, but the external conditions are pushed into the interior of the view.

. If the join predicate push had not been made, the index on the emp1 table would not be used when fetching the internal data of the view, and then the whole table would be scanned.

Step 1: then the two result sets are looped and nested to get the result.

Let's verify that the join predicate is not pushed, and the emp1 index is not taken when fetching the view dataset, but the full table scan emp1.

Select / * + no_merge (emp_view) no_push_pred (emp_view) * / emp.empno from emp,emp_view where emp.empno=emp_view.empno1 (+) and emp.ename='FROD'

Execution Plan

Plan hash value: 3053348535

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

-

| | 0 | SELECT STATEMENT | | 1 | 23 | 6 (17) | 00:00:01 |

| | 1 | MERGE JOIN OUTER | | 1 | 23 | 6 (17) | 00:00:01 |

| | * 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 10 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | VIEW | EMP_VIEW | 14 | 182 | 3 (0) | 00:00:01 |

| | 6 | TABLE ACCESS FULL | EMP1 | 14 | 182 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

2-filter ("EMP". "ENAME" = 'FROD')

4-access ("EMP". "EMPNO" = "EMP_VIEW". "EMPNO1" (+))

Filter ("EMP". "EMPNO" = "EMP_VIEW". "EMPNO1" (+))

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

11 recursive calls

0 db block gets

9 consistent gets

1 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

At this time, you can see that the emp table is fully indexed and returned to the table using the condition "EMP". "ENAME" = 'FROD' to get the dataset; instead of taking the emp1 index, the view scans the whole table, sorts the results, and then sorts and joins with the first result set.

Example sql:

Select emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD'

Execution Plan

Plan hash value: 2223410919

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | |

-

| | 0 | SELECT STATEMENT | | 2 | 24 | 5 (0) | |

| | 1 | NESTED LOOPS | | 2 | 24 | 5 (0) |

| | * 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0) | |

| | 3 | VIEW | EMP_VIEW_UNION | 1 | 2 | 2 (0) |

| | 4 | UNION ALL PUSHED PREDICATE |

| | * 5 | INDEX RANGE SCAN | IDX_EMP1 | 1 | 13 | 1 (0) |

| | * 6 | INDEX RANGE SCAN | IDX_EMP2 | 1 | 13 | 1 (0) |

-

Predicate Information (identified by operation id):

2-filter ("EMP". "ENAME" = 'FROD')

5-access ("EMP1". "EMPNO" = "EMP". "EMPNO")

6-access ("EMP2". "EMPNO" = "EMP". "EMPNO")

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

28 recursive calls

0 db block gets

39 consistent gets

0 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

Because there is union all in the view definition, EMP_VIEW_UNION cannot do view merge, but it can do join predicate push, so you can see that steps 5 and 6 push the join condition into the view, thus leaving the index of the emp1 and emp2 tables. Then the result set is looped and joined with the result set of ename=frod obtained by full table scan emp table, and the final result is obtained.

Similarly, if the join predicate push is prevented, the result set within the view is scanned according to the full table.

Select / * + no_push_pred (emp_view_union) * / emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD'

Execution Plan

Plan hash value: 894575737

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | |

| | 0 | SELECT STATEMENT | | 2 | 46 | 9 (12) | |

| | 1 | MERGE JOIN | | 2 | 46 | 9 (12) | |

| | * 2 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 10 | 2 (0) | |

| | 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0) |

| | * 4 | SORT JOIN | | 28 | 364 | 7 (15) |

| | 5 | VIEW | EMP_VIEW_UNION | 28 | 364 | 6 (0) | |

| | 6 | UNION-ALL |

| | 7 | TABLE ACCESS FULL | EMP1 | 14 | 182 | 3 (0) | |

| | 8 | TABLE ACCESS FULL | EMP2 | 14 | 182 | 3 (0) | |

Predicate Information (identified by operation id):

2-filter ("EMP". "ENAME" = 'FROD')

4-access ("EMP". "EMPNO" = "EMP_VIEW_UNION". "EMPNO1")

Filter ("EMP". "EMPNO" = "EMP_VIEW_UNION". "EMPNO1")

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

14 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

Note: whether the predicate push can be done has nothing to do with whether the view can be merged or whether it is an embedded view, but has something to do with the type of target view, the type of connection between external queries and the join method.

The following is a sql that cannot be pushed into a predicate:

Reason: the right side of the outer link of the view.

Select / * + no_merge (emp_view) use_nl (emp_view) push_pred (emp_view) * / emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and ename='FROD'

Execution Plan

Plan hash value: 3774177413

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 1 | 23 | 6 (0) | 00:00:01 |

| | 1 | NESTED LOOPS | | 1 | 23 | 6 (0) | 00:00:01 |

| | * 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0) | 00:00:01 |

| | * 3 | VIEW | EMP_VIEW | 1 | 13 | 3 (0) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | EMP1 | 14 | 182 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("ENAME" = 'FROD')

3-filter ("EMP". "EMPNO" = "EMP_VIEW". "EMPNO1")

Note

-

-dynamic sampling used for this statement (level=2)

Statistics

11 recursive calls

0 db block gets

14 consistent gets

0 physical reads

0 redo size

333 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

0 rows processed

Join predicate push condition:

There is union all/union/group by/distinct in the view definition statement

There are external joins, semi-joins, and anti-joins between views and external queries.

As long as one of the above conditions is met, predicates can be pushed, such as inner joins, but there is union all in the view definition statement.

Such as the example sql:select emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD' above

So much for sharing on how to understand the join predicate push in SQL optimization. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

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.