[20160219]關於連線順序.txt
[20160219]關於連線順序.txt
--今天被問一個問題，如果使用外連線的情況，連線順序可以改變嗎？我只能說我給測試看看，再回答這個問題：
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 | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | MERGE JOIN OUTER | | 14 | 812 | 6 (17)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
--看是否可以提示改變連線順序。
3.繼續測試：
select /*+ leading(emp dept) use_hash(dept) */ * from dept,emp where dept.deptno=emp.deptno(+);
Plan hash value: 2251696546
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| | | | |
| 1 | MERGE JOIN OUTER | | 14 | 812 | 6 (17)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 14 | 532 | 4 (25)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 | | | |
--------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
--可以發現提示無效。視乎存在外連線左右連線的情況無法改變連線順序，這個在最佳化sql語句中注意。
4.繼續測試，建立emp.ename索引,並且唯一。
SCOTT@book> select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename='KING';
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
Plan hash value: 3311718186
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | NESTED LOOPS | | 1 | 58 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_EMP_ENAME | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| |
---------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$C30CFC9A
2 - SEL$C30CFC9A / EMP@SEL$1
3 - SEL$C30CFC9A / EMP@SEL$1
4 - SEL$C30CFC9A / DEPT@SEL$1
5 - SEL$C30CFC9A / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."ENAME"='KING')
5 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
--這個時候視乎改變了連線順序，而且不存在外連線執行計劃。但是實際上這裡的+實際上多餘的。如果要實現外連線應該寫成：
select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)='KING';
SCOTT@book> select * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)='KING';
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Plan hash value: 965809594
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | | | |
| 1 | MERGE JOIN OUTER | | 4 | 232 | 5 (20)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 1 | 38 | 3 (34)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | | |
|* 6 | INDEX UNIQUE SCAN | I_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
6 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - access("EMP"."ENAME"='KING')
--如果使用提示：
select /*+ leading(emp dept) */ * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename(+)='KING';
Plan hash value: 965809594
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| | | | |
| 1 | MERGE JOIN OUTER | | 4 | 232 | 5 (20)| 00:00:01 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 80 | 2 (0)| 00:00:01 | | | |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 | | | |
|* 4 | SORT JOIN | | 1 | 38 | 3 (34)| 00:00:01 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | | | |
|* 6 | INDEX UNIQUE SCAN | I_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPT@SEL$1
3 - SEL$1 / DEPT@SEL$1
5 - SEL$1 / EMP@SEL$1
6 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
6 - access("EMP"."ENAME"='KING')
--可以發現連線順序並沒有改變。
--可以看出存在外連線的情況下，無法改變連線順序的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1991306/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20160330]關於連線順序3.txt
- [20160220]關於連線順序2.txt
- 外連線與連線順序
- 表的連線順序是否很重要
- 關於 Promise 的執行順序Promise
- 關於RocketMQ的順序訊息MQ
- # 關於select關鍵字語句定義順序# 關於select關鍵字語句執行順序
- 關於defer執行順序問題
- 關於java的初始化順序Java
- Oracle確定連線方式的優先順序Oracle
- java字串連線和運算子優先順序Java字串
- 詳解表連線順序和連線方式是否影響查詢結果
- 關於JAVA中順序IO的基本操作Java
- 轉： 關於oracle 是按照寫入順序還是ROWID順序取資料Oracle
- 關於jvm載入類的實現順序JVM
- 關於面向連線與面向無連線
- php與資料庫連線如何實現資料的順序和倒序PHP資料庫
- 關於 SAP Spartacus Angular HTTP Interceptor 的攔截順序AngularHTTP
- 關於CSS樣式的優先順序問題CSS
- [20171120]關於find 軟連線問題.txt
- 【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
- 線性表之順序儲存結構