【執行計劃】Oracle 11gR2使用Full outer Joins執行計劃完成全外連線查詢
在文章《【FULL OUTER JOIN】全外連線的union all改寫方法》(http://space.itpub.net/519536/viewspace-618053)中給大家介紹過關於全外連線的使用方法及改寫方法。Oracle 11gR2版本中使用了全新的執行計劃Full outer Joins來支援全外連線查詢,提高了檢索效率。這裡對比一下union寫法和全外連線寫法他們的執行計劃的不同。
1.建立實驗表並初始化實驗資料
sys@ora11g> select * from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
conn sec/sec
create table a (a number(1),b number(1),c number(1));
create table b (a number(1),d number(1),e number(1));
insert into a values(1,1,1);
insert into a values(2,2,2);
insert into a values(3,3,3);
insert into b values(1,4,4);
insert into b values(2,5,5);
insert into b values(4,6,6);
commit;
2.兩種實現全外連線查詢的方法
1)第一種方法:使用兩次外連線結合union的方法
select * from a,b where a.a=b.a(+)
union
select * from a,b where a.a(+)=b.a;
2)第二種方法:使用全外連線標準寫法
select * from a full outer join b on a.a = b.a;
3.比較兩種執行方法的執行計劃
1)反覆執行第一種方法得到穩定的執行計劃
sec@ora11g> select * from a,b where a.a=b.a(+)
2 union
3 select * from a,b where a.a(+)=b.a;
A B C A D E
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 4 4
2 2 2 2 5 5
3 3 3
4 6 6
Execution Plan
----------------------------------------------------------
Plan hash value: 891669117
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 468 | 15 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 468 | 15 (60)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 117 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 117 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."A"="B"."A"(+))
6 - access("A"."A"(+)="B"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
792 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
2)反覆執行第二種方法得到穩定的執行計劃
sec@ora11g> select * from a full outer join b on a.a = b.a;
A B C A D E
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 4 4
2 2 2 2 5 5
4 6 6
3 3 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3456740935
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 234 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 3 | 234 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 3 | 234 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | A | 3 | 117 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | B | 3 | 117 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."A"="B"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
792 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
3)執行計劃對比結果
第一種外連線結合union方法對錶進行了兩次掃描,而全外連線方法引入了HASH JOIN FULL OUTER執行計劃僅需對錶進行一次掃描便得到了查詢結果。從consistent gets上也很直觀,全外連線寫法(28)比union方法(15)的consistent gets少了一半。
4.小結
Oracle對全外連線的支援越來越好,從最初Oracle不支援全外連線的SQL寫法,到支援SQL的特定寫法,同時從執行計劃上也在逐漸最佳化中。
Good luck.
secooler
11.11.01
-- The End --
1.建立實驗表並初始化實驗資料
sys@ora11g> select * from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
conn sec/sec
create table a (a number(1),b number(1),c number(1));
create table b (a number(1),d number(1),e number(1));
insert into a values(1,1,1);
insert into a values(2,2,2);
insert into a values(3,3,3);
insert into b values(1,4,4);
insert into b values(2,5,5);
insert into b values(4,6,6);
commit;
2.兩種實現全外連線查詢的方法
1)第一種方法:使用兩次外連線結合union的方法
select * from a,b where a.a=b.a(+)
union
select * from a,b where a.a(+)=b.a;
2)第二種方法:使用全外連線標準寫法
select * from a full outer join b on a.a = b.a;
3.比較兩種執行方法的執行計劃
1)反覆執行第一種方法得到穩定的執行計劃
sec@ora11g> select * from a,b where a.a=b.a(+)
2 union
3 select * from a,b where a.a(+)=b.a;
A B C A D E
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 4 4
2 2 2 2 5 5
3 3 3
4 6 6
Execution Plan
----------------------------------------------------------
Plan hash value: 891669117
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 468 | 15 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 468 | 15 (60)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 117 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 117 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."A"="B"."A"(+))
6 - access("A"."A"(+)="B"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
792 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
2)反覆執行第二種方法得到穩定的執行計劃
sec@ora11g> select * from a full outer join b on a.a = b.a;
A B C A D E
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 4 4
2 2 2 2 5 5
4 6 6
3 3 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3456740935
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 234 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 3 | 234 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 3 | 234 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | A | 3 | 117 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | B | 3 | 117 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."A"="B"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
792 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
3)執行計劃對比結果
第一種外連線結合union方法對錶進行了兩次掃描,而全外連線方法引入了HASH JOIN FULL OUTER執行計劃僅需對錶進行一次掃描便得到了查詢結果。從consistent gets上也很直觀,全外連線寫法(28)比union方法(15)的consistent gets少了一半。
4.小結
Oracle對全外連線的支援越來越好,從最初Oracle不支援全外連線的SQL寫法,到支援SQL的特定寫法,同時從執行計劃上也在逐漸最佳化中。
Good luck.
secooler
11.11.01
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-710043/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- explain 查詢執行計劃AI
- 執行計劃-6:推入子查詢
- oracle 固定執行計劃Oracle
- Oracle sql執行計劃OracleSQL
- 【執行計劃】Oracle獲取執行計劃的幾種方法Oracle
- [oracle] 查詢歷史會話、歷史執行計劃Oracle會話
- Oracle執行計劃Explain Plan 如何使用OracleAI
- oracle使用outline固定執行計劃事例Oracle
- 執行計劃-1:獲取執行計劃
- 在MySQL中使用explain查詢SQL的執行計劃MySqlAI
- Oracle-繫結執行計劃Oracle
- 分析執行計劃優化SQLORACLE的執行計劃(轉)優化SQLOracle
- Oracle調優之看懂Oracle執行計劃Oracle
- MySQL執行計劃MySql
- SYBASE執行計劃
- MySQL 執行計劃MySql
- Oracle檢視執行計劃的命令Oracle
- Sqlserver執行計劃中表的四種連線方式SQLServer
- Oracle SQL Profile固定執行計劃的方法OracleSQL
- Oracle緊急固定執行計劃之手段Oracle
- MySQL執行計劃解析MySql
- mysql explain 執行計劃MySqlAI
- explain執行計劃分析AI
- mysql執行計劃explainMySqlAI
- 執行計劃執行步驟原則
- 【PG執行計劃】Postgresql資料庫執行計劃統計資訊簡述SQL資料庫
- PostgreSQL 查詢當前執行中sql的執行計劃——pg_show_plans模組SQL
- Oracle 通過註釋改變執行計劃Oracle
- 知識篇 | ORACLE 如何執行計劃繫結Oracle
- Oracle如何檢視真實執行計劃(一)Oracle
- Oracle如何手動重新整理執行計劃Oracle
- mongodb執行計劃解釋MongoDB
- 檢視 OceanBase 執行計劃
- MySQL執行計劃解析(四)MySql
- 讀懂MySQL執行計劃MySql
- Explain執行計劃詳解AI
- PostgreSQL執行計劃變化SQL
- .Oracle固定執行計劃之SQL PROFILE概要檔案OracleSQL
- 在Oracle中,如何得到真實的執行計劃?Oracle