[20160330]關於連線順序3.txt
[20160330]關於連線順序3.txt
--關於連線順序，曾經寫過兩篇blog，連結如下：
http://blog.itpub.net/267265/viewspace-1991306/
http://blog.itpub.net/267265/viewspace-1991787/
--今天才發現自己犯了一個嚴重錯誤，使用外連線的情況，連線順序是可以改變的，以前的blog存在嚴重錯誤-(:)
--還是透過例子來講解：
1.環境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試：
select * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 2251696546
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 15 |00:00:00.01 | 10 | | | |
| 1 | MERGE JOIN OUTER | | 1 | 14 | 812 | 6 (17)| 00:00:01 | 15 |00:00:00.01 | 10 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 4 | 80 | 2 (0)| 00:00:01 | 4 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | | 1 (0)| 00:00:01 | 4 |00:00:00.01 | 2 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 532 | 4 (25)| 00:00:01 | 14 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
--我前面的測試使用提示leading來指定連線順序，不生效。
select /*+ full(dept) full(emp) leading( emp dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3713469723
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 15 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 14 | 812 | 6 (0)| 00:00:01 | 15 |00:00:00.01 | 13 | 1321K| 1321K| 1040K (0)|
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 7 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
--可以發現leading並沒有改變連線順序。感覺在oracle的提示上很奇怪。時間上昨天看連結：
--
--
--才發現自己犯了一個低階錯誤。透過提示swap_join_inputs就可以改變連線順序，很奇怪的是這種情況下leading提示無效。
3.繼續測試：
select /*+ full(dept) full(emp) leading( emp dept) swap_join_inputs(emp)*/ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 15 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN RIGHT OUTER| | 1 | 14 | 812 | 6 (0)| 00:00:01 | 15 |00:00:00.01 | 13 | 1048K| 1048K| 656K (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
--可以發現連線順序發生了變化。連線操作變成了HASH JOIN RIGHT OUTER(原來是HASH JOIN OUTER)。
--也就是將使用外連線是可以改變連線順序的。還可以發現在這樣的情況前面的leading實際上是錯誤的提示。
select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp)*/ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 15 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN RIGHT OUTER| | 1 | 14 | 812 | 6 (0)| 00:00:01 | 15 |00:00:00.01 | 13 | 1048K| 1048K| 654K (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
4.但是如果連線是nested loop，merge無法改變連線順序的。
select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_nl(dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 15 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN RIGHT OUTER| | 1 | 14 | 812 | 6 (0)| 00:00:01 | 15 |00:00:00.01 | 13 | 1048K| 1048K| 717K (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_nl(emp) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 2022884187
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 10 (100)| | 15 |00:00:00.01 | 32 |
| 1 | NESTED LOOPS OUTER| | 1 | 14 | 812 | 10 (0)| 00:00:01 | 15 |00:00:00.01 | 32 |
| 2 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 4 | 152 | 2 (0)| 00:00:01 | 14 |00:00:00.01 | 25 |
---------------------------------------------------------------------------------------------------------------------
select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_merge(dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 3590956717
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 15 |00:00:00.01 | 13 | | | |
|* 1 | HASH JOIN RIGHT OUTER| | 1 | 14 | 812 | 6 (0)| 00:00:01 | 15 |00:00:00.01 | 13 | 1048K| 1048K| 659K (0)|
| 2 | TABLE ACCESS FULL | EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL | DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 7 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
select /*+ full(dept) full(emp) leading( dept emp) swap_join_inputs(emp) use_merge(emp) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 1800246806
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 8 (100)| | 15 |00:00:00.01 | 12 | | | |
| 1 | MERGE JOIN OUTER | | 1 | 14 | 812 | 8 (25)| 00:00:01 | 15 |00:00:00.01 | 12 | | | |
| 2 | SORT JOIN | | 1 | 4 | 80 | 4 (25)| 00:00:01 | 4 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 3 | TABLE ACCESS FULL| DEPT | 1 | 4 | 80 | 3 (0)| 00:00:01 | 4 |00:00:00.01 | 6 | | | |
|* 4 | SORT JOIN | | 4 | 14 | 532 | 4 (25)| 00:00:01 | 14 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL| EMP | 1 | 14 | 532 | 3 (0)| 00:00:01 | 14 |00:00:00.01 | 6 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------
--視乎不存在NESTED LOOPS RIGHT OUTER,MERGE JOIN RIGHT OUTER操作，也許我還是不對。
--google找到一些連線：
http://newleague.iteye.com/blog/1001842
那麼我們為什麼不支援right outer join呢。在這裡,我們想返回符合條件的行對(R1,R2) 和不符合連線條件的(NULL,R2)。問題是我們會
多次掃描內部表-對於外部表的每行都要掃描一次。 在多次掃描過程中我們可能會多次處理內部表的同一行。這樣我們就無法來判斷某一
行到底符合 不符合連線條件。更進一步,如果我們使用index join,一些內部行可能都不會被處理,但是這些行在 外連線時是應該返回的
。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2072164/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20160219]關於連線順序.txt
- 外連線與連線順序
- [20160220]關於連線順序2.txt
- 表的連線順序是否很重要
- 關於 Promise 的執行順序Promise
- 關於defer執行順序問題
- 關於java的初始化順序Java
- Oracle確定連線方式的優先順序Oracle
- 詳解表連線順序和連線方式是否影響查詢結果
- 關於JAVA中順序IO的基本操作Java
- 轉： 關於oracle 是按照寫入順序還是ROWID順序取資料Oracle
- 關於jvm載入類的實現順序JVM
- 關於面向連線與面向無連線
- php與資料庫連線如何實現資料的順序和倒序PHP資料庫
- 關於 SAP Spartacus Angular HTTP Interceptor 的攔截順序AngularHTTP
- 關於CSS樣式的優先順序問題CSS
- 【Oracle】-【插入讀取順序】-插入讀取之間的順序關係Oracle
- 關於describe和test執行順序的翻譯
- 關於jetty連線關閉問題Jetty
- 線性表順序儲存優缺點，線性連結串列的優缺點
- css中連結偽類的順序CSS
- Mysql關於長連線短連線優劣比較MySql
- 線性表的使用——順序實現
- Python--關於連線符+Python
- 關於oracle中的反連線Oracle
- 關於oracle中的半連線Oracle
- 關於mysql連線慢的分析.MySql
- 關於mysql連線的問題MySql
- 關於連線池的問題！
- 關於資料庫連線池資料庫
- 關於Java中try-catch-finally-return的執行順序Java
- Mysql關於my.cnf引數檔案的讀取順序及各項配置的覆蓋優先順序MySql
- mysql 中sql語句關鍵字的書寫順序與執行順序MySql
- 線性表之順序儲存結構
- 資料結構 - 線性表 - 順序表資料結構
- 關於 Homestead 連線 MySQL 問題MySql
- 關於外連線和where條件
- 關於java連線SQL資料庫JavaSQL資料庫