[20160220]关于连接顺序2.txt

[20160220]关于连接顺序2.txt

--前几天被问一个问题，如果使用外连接的情况，连接顺序可以改变吗？我只能说我给测试看看，再回答这个问题：
--链接测试已经说明存在外连接的情况下，无法改变连接顺序的。

--今天想想忘记考虑全连接的情况,继续测试:

1.环境:
SCOTT@test01p> @ ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.首先看看上次测试执行的语句:

select  * from dept,emp where dept.deptno=emp.deptno(+) and emp.ename='KING';

--我提到实际上上面的语句没有使用外连接,通过10053事件看看:

SCOTT@test01p> @10053on 12
Session altered.

SCOTT@test01p> 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

SCOTT@test01p> @10053off
Session altered.

--看看转储,可以发现最终转换
Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "DEPT"."DEPTNO" "DEPTNO","DEPT"."DNAME" "DNAME","DEPT"."LOC" "LOC","EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR"
       "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO"
  FROM "SCOTT"."DEPT" "DEPT","SCOTT"."EMP" "EMP"
 WHERE "DEPT"."DEPTNO" = "EMP"."DEPTNO"
   AND "EMP"."ENAME"   = 'KING'
kkoqbc: optimizing query block SEL$C30CFC9A (#0)

--可以发现外连接实际上是取消了.煮为了显示的需要,做了格式化处理.

3.看看全连接的情况:

select /*+ leading(emp dept) */  * from dept full join emp on dept.deptno=emp.deptno ;
select /*+ leading(emp dept) */  * from emp full join dept on dept.deptno=emp.deptno ;

Plan hash value: 51889263

--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        |       |     6 (100)|          |       |       |          |
|   1 |  VIEW                 | VW_FOJ_0 |     15 |  1755 |     6   (0)| 00:00:01 |       |       |          |
|*  2 |   HASH JOIN FULL OUTER|          |     15 |   870 |     6   (0)| 00:00:01 |  1321K|  1321K| 1060K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT     |      4 |    80 |     3   (0)| 00:00:01 |       |       |          |
|   4 |    TABLE ACCESS FULL  | EMP      |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / from$_subquery$_003@SEL$2
   2 - SEL$1
   3 - SEL$1 / DEPT@SEL$1
   4 - SEL$1 / EMP@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

--以上2种写法执行计划都是一样.再来看看10053事件.

SCOTT@test01p> @ 10053on 12
Session altered.

Select /*+ leading(emp dept) */  * from dept full join emp on dept.deptno=emp.deptno ;

SCOTT@test01p> @ 10053off
Session altered.

--检查转储文件:

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT /*+ LEADING ("EMP" "DEPT") */ "VW_FOJ_0"."DEPTNO_0" "DEPTNO","VW_FOJ_0"."DNAME_1"
       "DNAME","VW_FOJ_0"."LOC_2" "LOC","VW_FOJ_0"."EMPNO_3" "EMPNO","VW_FOJ_0"."ENAME_4" "ENAME","VW_FOJ_0"."JOB_5"
       "JOB","VW_FOJ_0"."MGR_6" "MGR","VW_FOJ_0"."HIREDATE_7" "HIREDATE","VW_FOJ_0"."SAL_8" "SAL","VW_FOJ_0"."COMM_9"
       "COMM","VW_FOJ_0"."DEPTNO_10" "DEPTNO"
  FROM (
        SELECT "DEPT"."DEPTNO" "DEPTNO_0","DEPT"."DNAME" "DNAME_1","DEPT"."LOC" "LOC_2","EMP"."EMPNO"
               "EMPNO_3","EMP"."ENAME" "ENAME_4","EMP"."JOB" "JOB_5","EMP"."MGR" "MGR_6","EMP"."HIREDATE"
               "HIREDATE_7","EMP"."SAL" "SAL_8","EMP"."COMM" "COMM_9","EMP"."DEPTNO" "DEPTNO_10"
          FROM "SCOTT"."EMP" "EMP"
          FULL OUTER JOIN "SCOTT"."DEPT" "DEPT"
    ON "DEPT"."DEPTNO" = "EMP"."DEPTNO") "VW_FOJ_0"

kkoqbc: optimizing query block SEL$1 (#0)

--提示无效,理论讲这个那个在前在后都可以.

SCOTT@test01p> EXECUTE SYS.DBMS_STATS.SET_TABLE_STATS (OWNNAME=>user, TABNAME=>'dept', NUMROWS=> 1000,NO_INVALIDATE=>false);
PL/SQL procedure successfully completed.

Select /*+ leading(emp dept) */  * from dept full outer join emp on dept.deptno=emp.deptno ;
Plan hash value: 3194568465
--------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |        |       |     6 (100)|          |       |       |          |
|   1 |  VIEW                 | VW_FOJ_0 |   3750 |   428K|     6   (0)| 00:00:01 |       |       |          |
|*  2 |   HASH JOIN FULL OUTER|          |   3750 |   212K|     6   (0)| 00:00:01 |  1048K|  1048K|  646K (0)|
|   3 |    TABLE ACCESS FULL  | EMP      |     14 |   532 |     3   (0)| 00:00:01 |       |       |          |
|   4 |    TABLE ACCESS FULL  | DEPT     |   1000 | 20000 |     3   (0)| 00:00:01 |       |       |          |
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / from$_subquery$_003@SEL$2
   2 - SEL$1
   3 - SEL$1 / EMP@SEL$1
   4 - SEL$1 / DEPT@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

--不知道使用什么提示改变全连接顺序.