[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语句时注意这个问题.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2133030/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2133030/