如何理解SQL優(yōu)化中連接謂詞推入
這篇文章將為大家詳細(xì)講解有關(guān)如何理解SQL優(yōu)化中連接謂詞推入，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對相關(guān)知識有一定的了解。
創(chuàng)新互聯(lián)長期為上千客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對不同對象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為塔城企業(yè)提供專業(yè)的做網(wǎng)站、網(wǎng)站設(shè)計(jì)，塔城網(wǎng)站改版等技術(shù)服務(wù)。擁有十余年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。
SQL優(yōu)化之連接謂詞推入：
環(huán)境準(zhǔn)備：
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;
賦權(quán)，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視圖左外連接，視圖是補(bǔ)充表。
查看執(zhí)行計(jì)劃：
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
該執(zhí)行計(jì)劃比較好理解：步驟2與步驟3同級，但是步驟2沒有子ID，所以最先執(zhí)行步驟2.
步驟2：該步驟有一個(gè)filter條件filter("EMP"."ENAME"='FROD')，全表掃描emp表，找出ename=frod的所有數(shù)據(jù)
步驟4：索引范圍掃描，目標(biāo)條件滿足access("EMP1"."EMPNO"="EMP"."EMPNO")，這里把視圖和表左外連接的條件推入到了視圖中。
步驟3：VIEW PUSHED PREDICATE說明沒有做視圖合并，把視圖當(dāng)做一個(gè)獨(dú)立單元來執(zhí)行，但是把外部條件推入到了視圖內(nèi)部
。如果沒有做這次連接謂詞推入，那么就不會(huì)在抓取視圖內(nèi)部數(shù)據(jù)的時(shí)候用到emp1表上的索引，那樣的話就會(huì)全表掃描了。
步驟1：然后兩個(gè)結(jié)果集做循環(huán)嵌套外連接，得到結(jié)果。
下面驗(yàn)證一下，連接謂詞未推入，抓取視圖數(shù)據(jù)集的時(shí)候不會(huì)走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
這時(shí)可以看到對emp表進(jìn)行索引全掃描，利用條件"EMP"."ENAME"='FROD'回表，得到數(shù)據(jù)集；視圖并沒有走emp1的索引，而是全表掃描，并將結(jié)果進(jìn)行排序，然后與第一個(gè)結(jié)果集進(jìn)行排序合并外連接。
范例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
因?yàn)橐晥D定義中有union all，所以EMP_VIEW_UNION不能做視圖合并，但是可以做連接謂詞推入，所以看到步驟5和步驟6將連接條件推入到了視圖內(nèi)部，從而走了emp1和emp2表的索引。然后將結(jié)果集與全表掃描emp表得到的ename=frod的結(jié)果集做循環(huán)嵌套連接，得到最終結(jié)果。
同樣地，如果阻止了連接謂詞推入，那么視圖內(nèi)部結(jié)果集會(huì)按照全表掃描。
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
注意：能否做謂詞推入，與視圖能否合并，是否是內(nèi)嵌視圖沒有關(guān)系，與目標(biāo)視圖的類型，與外部查詢之間的連接類型以及連接方法有關(guān)。
如下是一個(gè)無法謂詞推入的sql：
原因：視圖在外鏈接的右側(cè)。
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
視圖與外部查詢之間是外連接，半連接，反連接
以上只要滿足一種條件就可以謂詞推入，比如內(nèi)連接，但是視圖定義語句中有union all。
如上面的范例sql：select emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD';
關(guān)于如何理解SQL優(yōu)化中連接謂詞推入就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。
文章名稱：如何理解SQL優(yōu)化中連接謂詞推入
當(dāng)前地址：http://m.jiaotiyi.com/article/pedgcs.html