共計 9045 個字符，預計需要花費 23 分鐘才能閱讀完成。
這篇文章將為大家詳細講解有關如何理解 SQL 優化中連接謂詞推入，文章內容質量較高，因此丸趣 TV 小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。
SQL 優化之連接謂詞推入：
環境準備：
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;
賦權，scott 用戶可以開啟 set autot
grant select on v_$sesstat to scott;
grant select on v_$statname to scott;
grant select on v_$mystat to scott;
sql 范例 1:
select /*+ no_merge(emp_view) */emp.empno from emp,emp_view where emp.empno=emp_view.empno1(+) and emp.ename= FROD
可以看到 emp 表和 emp_view 視圖左外連接，視圖是補充表。
查看執行計劃：
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
該執行計劃比較好理解：步驟 2 與步驟 3 同級，但是步驟 2 沒有子 ID，所以最先執行步驟 2.
步驟 2：該步驟有一個 filter 條件 filter(EMP . ENAME = FROD)，全表掃描 emp 表，找出 ename=frod 的所有數據
步驟 4：索引范圍掃描，目標條件滿足 access(EMP1 . EMPNO = EMP . EMPNO)，這里把視圖和表左外連接的條件推入到了視圖中。
步驟 3：VIEW PUSHED PREDICATE 說明沒有做視圖合并，把視圖當做一個獨立單元來執行，但是把外部條件推入到了視圖內部
。如果沒有做這次連接謂詞推入，那么就不會在抓取視圖內部數據的時候用到 emp1 表上的索引，那樣的話就會全表掃描了。
步驟 1：然后兩個結果集做循環嵌套外連接，得到結果。
下面驗證一下，連接謂詞未推入，抓取視圖數據集的時候不會走 emp1 的索引，而是全表掃描 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
這時可以看到對 emp 表進行索引全掃描，利用條件 EMP . ENAME = FROD 回表，得到數據集；視圖并沒有走 emp1 的索引，而是全表掃描，并將結果進行排序，然后與第一個結果集進行排序合并外連接。
范例 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
因為視圖定義中有 union all，所以 EMP_VIEW_UNION 不能做視圖合并，但是可以做連接謂詞推入，所以看到步驟 5 和步驟 6 將連接條件推入到了視圖內部，從而走了 emp1 和 emp2 表的索引。然后將結果集與全表掃描 emp 表得到的 ename=frod 的結果集做循環嵌套連接，得到最終結果。
同樣地，如果阻止了連接謂詞推入，那么視圖內部結果集會按照全表掃描。
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
注意：能否做謂詞推入，與視圖能否合并，是否是內嵌視圖沒有關系，與目標視圖的類型，與外部查詢之間的連接類型以及連接方法有關。
如下是一個無法謂詞推入的 sql：
原因：視圖在外鏈接的右側。
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
連接謂詞推入條件：
視圖定義語句中存在 union all/union/group by/distinct
視圖與外部查詢之間是外連接，半連接，反連接
以上只要滿足一種條件就可以謂詞推入，比如內連接，但是視圖定義語句中有 union all。
如上面的范例 sql：select emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename= FROD
關于如何理解 SQL 優化中連接謂詞推入就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
