[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')

--可以发现连接顺序并没有改变。
--可以看出存在外连接的情况下，无法改变连接顺序的。