[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的提示上很奇怪。时间上昨天看链接：
--https://jonathanlewis.wordpress.com/2016/03/17/hinting-4/
--https://jonathanlewis.wordpress.com/2010/12/10/quiz-night-10/
--才发现自己犯了一个低级错误。通过提示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,一些内部行可能都不会被处理,但是这些行在 外连接时是应该返回的
。
