[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,一些内部行可能都不会被处理,但是这些行在 外连接时是应该返回的
。