[20170203]12c left right 外連線的增強

[20170203]12c left right 外連線的增強.txt

--相關連結:http://blog.itpub.net/267265/viewspace-1593068/
--連結提到我個人更加喜歡使用(+)的風格,如果使用10053跟蹤,oracle sql內部引擎實際轉化為(+)語法.
--測試一下12c在這方面的增強:

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

create table t1 as select rownum   id,rownum||'t1' data from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' data from dual connect by level<=5;
create table t3 as select rownum+2 id,rownum||'t3' data from dual connect by level<=5;

--分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '

2.測試:
--如果在11g下寫成如下是無法執行的.

SCOTT@book> SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND t3.id = t2.id(+) ORDER BY 1;
SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND t3.id = t2.id(+) ORDER BY 1
                                                     *
ERROR at line 1:
ORA-01417: a table may be outer joined to at most one other table

$ oerr ora 01417
01417, 00000, "a table may be outer joined to at most one other table"
// *Cause:  a.b (+) = b.b and a.c (+) = c.c is not allowed
// *Action: Check that this is really what you want, then join b and c first
//          in a view.

--在11g要改寫如下:
with tt as (SELECT t1.id t1_id,t1.data t1_data,t2.id t2_id,t2.data t2_data FROM t1 , t2 WHERE t1.id = t2.id(+) )
select tt.*,t3.* from  tt,t3 where t3.id = tt.t2_id(+) ORDER BY 1;

T1_ID T1_DATA T2_ID T2_DATA ID DATA
----- ------- ----- ------- --- -----
    3 3t1         3 2t2      3 1t3
    4 4t1         4 3t2      4 2t3
    5 5t1         5 4t2      5 3t3
                             6 4t3
                             7 5t3

--//不對,看下面的連結...

3.12c呢?
SCOTT@ztest> @ 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

create table t1 as select rownum   id,rownum||'t1' data from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' data from dual connect by level<=5;
create table t3 as select rownum+2 id,rownum||'t3' data from dual connect by level<=5;

--分析略.Method_Opt => 'FOR ALL COLUMNS SIZE 1 '

SCOTT@ztest> set null null
SCOTT@ztest> SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND t3.id = t2.id(+) ORDER BY 1;
ID DATA          ID DATA  ID DATA
--- ----- ---------- ----- -- ------
  1 1t1   null       null   5 3t3
  1 1t1   null       null   7 5t3
  1 1t1   null       null   6 4t3
  1 1t1   null       null   4 2t3
  1 1t1   null       null   3 1t3
  2 2t1   null       null   5 3t3
  2 2t1   null       null   6 4t3
  2 2t1   null       null   3 1t3
  2 2t1   null       null   7 5t3
  2 2t1   null       null   4 2t3
  3 3t1   null       null   6 4t3
  3 3t1   null       null   4 2t3
  3 3t1   null       null   5 3t3
  3 3t1            3 2t2    3 1t3
  3 3t1   null       null   7 5t3
  4 4t1            4 3t2    4 2t3
  4 4t1   null       null   6 4t3
  4 4t1   null       null   7 5t3
  4 4t1   null       null   3 1t3
  4 4t1   null       null   5 3t3
  5 5t1   null       null   3 1t3
  5 5t1   null       null   7 5t3
  5 5t1            5 4t2    5 3t3
  5 5t1   null       null   4 2t3
  5 5t1   null       null   6 4t3
25 rows selected.
--//不過結果相差甚遠....^_^.看看執行計劃:

SCOTT@ztest> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  brzjuhsm3u3uq, child number 0
-------------------------------------
SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND
t3.id = t2.id(+) ORDER BY 1
Plan hash value: 3763024351
-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |        |       |    13 (100)|          |       |       |          |
|   1 |  SORT ORDER BY         |      |     25 |   525 |    13   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|*  2 |   HASH JOIN OUTER      |      |     25 |   525 |    13   (0)| 00:00:01 |  1451K|  1451K| 1331K (0)|
|   3 |    MERGE JOIN CARTESIAN|      |     25 |   350 |    10   (0)| 00:00:01 |       |       |          |
|   4 |     TABLE ACCESS FULL  | T1   |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
|   5 |     BUFFER SORT        |      |      5 |    35 |     7   (0)| 00:00:01 |  2048 |  2048 | 2048  (0)|
|   6 |      TABLE ACCESS FULL | T3   |      5 |    35 |     1   (0)| 00:00:01 |       |       |          |
|   7 |    TABLE ACCESS FULL   | T2   |      5 |    35 |     3   (0)| 00:00:01 |       |       |          |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   4 - SEL$1 / T1@SEL$1
   6 - SEL$1 / T3@SEL$1
   7 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T1"."ID"="T2"."ID" AND "T3"."ID"="T2"."ID")

Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
39 rows selected.

--看來我理解連線錯誤嗎?11G寫成如下:
WITH tx
     AS (SELECT t1.id t1_id
               ,t1.data t1_data
               ,t3.id t3_id
               ,t3.data t3_data
           FROM t1, t3)
  SELECT tx.t1_id
        ,tx.t1_data
        ,T2.ID
        ,t2.data
        ,tx.t3_id
        ,tx.t3_data
    FROM tx, t2
   WHERE tx.t1_id = t2.id(+) AND tx.t3_id = t2.id(+)
ORDER BY 1;

--執行如下2者輸出都是no rows selected.

WITH tx
     AS (SELECT t1.id t1_id
               ,t1.data t1_data
               ,t3.id t3_id
               ,t3.data t3_data
           FROM t1, t3)
  SELECT tx.t1_id
        ,tx.t1_data
        ,T2.ID
        ,t2.data
        ,tx.t3_id
        ,tx.t3_data
    FROM tx, t2
   WHERE tx.t1_id = t2.id(+) AND tx.t3_id = t2.id(+)
minus
SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND t3.id = t2.id(+) ;

WITH tx
     AS (SELECT t1.id t1_id
               ,t1.data t1_data
               ,t3.id t3_id
               ,t3.data t3_data
           FROM t1, t3)
SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND t3.id = t2.id(+)
minus
SELECT tx.t1_id
        ,tx.t1_data
        ,T2.ID
        ,t2.data
        ,tx.t3_id
        ,tx.t3_data
    FROM tx, t2
   WHERE tx.t1_id = t2.id(+) AND tx.t3_id = t2.id(+);

--//我再思考的問題是如果12c以後會不會有人寫成:
SELECT t1.*,t2.*,t3.* FROM t1 , t2 , t3  WHERE t1.id = t2.id(+) AND t3.id = t2.id(+) ORDER BY 1;
--//而實際不需要這樣的結果集.只能在以後最佳化sql語句時注意這個問題.