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

--不知道使用什麼提示改變全連線順序.