ITPub博客

首页 > 数据库 > Oracle > [20200711]关于左右连接的问题.txt

[20200711]关于左右连接的问题.txt

原创 Oracle 作者:lfree 时间:2020-07-11 10:54:05 0 删除 编辑

[20200711]关于左右连接的问题.txt

--//以前写的,链接如下:http://blog.itpub.net/267265/viewspace-1593068/
--//仅仅做一些必要的补充.我个人的工作习惯使用(+)方式

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

create table t1 as select rownum   id,rownum    ||'t1' data from dual connect by level<=3;
create table t2 as select rownum+1 id,(rownum+1)||'t2' data from dual connect by level<=3;
--//分析略.

SCOTT@test01p> select * from t1;
 ID DATA
--- -----
  1 1t1
  2 2t1
  3 3t1

SCOTT@test01p> select * from t2;
 ID DATA
--- -----
  2 2t2
  3 3t2
  4 4t2

2.测试:
SCOTT@test01p> set null NULL
SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id ;
 ID DATA          ID DATA
--- ----- ---------- -----
  2 2t1            2 2t2
  3 3t1            3 3t2
  1 1t1   NULL       NULL
--//left join 相当于在右边写入(+),等效的写法是select * from t1 ,t2 where t1.id=t2.id(+) ;
--//我个人更加喜欢(+)的写法.但是如果如下语句:

SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id and t1.id=2;
 ID DATA          ID DATA
--- ----- ---------- ------
  2 2t1            2 2t2
  1 1t1   NULL       NULL
  3 3t1   NULL       NULL

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5katgcygcphpc, child number 0
-------------------------------------
select * from t1 left join t2 on t1.id=t2.id and t1.id=2
Plan hash value: 1823443478
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      3 |    42 |     6   (0)| 00:00:01 |  1743K|  1743K|  823K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / T1@SEL$1
   3 - SEL$2BFA4EE4 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T1"."ID"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN 2 ELSE 2 END )

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
 
--//注意看Predicate Information (identified by operation id):,acces是:
   1 - access("T1"."ID"="T2"."ID" AND "T1"."ID"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN 2 ELSE 2 END )
--//转换的sql语句查询条件发生了变化,以前我一直认为很难有开发写出这样的sql语句.

SCOTT@test01p> @ expand_sql_text12c.sql 5katgcygcphpc
old   4:         select sql_fulltext into m_sql_in from v$sqlarea where sql_id='&&1';
new   4:         select sql_fulltext into m_sql_in from v$sqlarea where sql_id='5katgcygcphpc';
PL/SQL procedure successfully completed.

M_SQL_OUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000","A1"."QCSJ_C000000000300002_1" "QCSJ_C000000000300002","A1"."QCSJ_C000000000300001_2" "QCSJ_C00000
0000300001","A1"."QCSJ_C000000000300003_3" "QCSJ_C000000000300003" FROM  (SELECT "A3"."ID" "QCSJ_C000000000300000_0","A3"."DATA" "QCSJ_C000000000300002_1","A2".
"ID" "QCSJ_C000000000300001_2","A2"."DATA" "QCSJ_C000000000300003_3" FROM "SCOTT"."T1" "A3","SCOTT"."T2" "A2" WHERE "A3"."ID"="A2"."ID" AND "A3"."ID"=2) "A1"

--//格式话如下:
SELECT "A1"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000",
       "A1"."QCSJ_C000000000300002_1" "QCSJ_C000000000300002",
       "A1"."QCSJ_C000000000300001_2" "QCSJ_C000000000300001",
       "A1"."QCSJ_C000000000300003_3" "QCSJ_C000000000300003"
  FROM (SELECT "A3"."ID" "QCSJ_C000000000300000_0",
       "A3"."DATA" "QCSJ_C000000000300002_1","A2"."ID" "QCSJ_C000000000300001_2",
       "A2"."DATA" "QCSJ_C000000000300003_3" FROM "SCOTT"."T1" "A3",
       "SCOTT"."T2" "A2" WHERE "A3"."ID" = "A2"."ID" AND "A3"."ID"=2) "A1"
--//明显看出这样的执行计划取消了外连接,根本不对,expand出了问题.不知道是否是bug.换另外的方式看看:

SCOTT@test01p> @ 10053x 5katgcygcphpc 0
PL/SQL procedure successfully completed.

--//检查转储发现,注:sql语句我做了格式化处理.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."DATA" "DATA","T2"."ID" "ID",
       "T2"."DATA" "DATA"
  FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
 WHERE "T1"."ID" = "T2"."ID"(+)
   AND "T1"."ID" =
       CASE
       WHEN ("T2"."ID"(+) IS NOT NULL) THEN 2
       ELSE 2
       END
kkoqbc: optimizing query block SEL$2BFA4EE4 (#0)

3.换成加号的写法:
--//前面的转化不做测试了.
SCOTT@test01p> select * from t1,t2 where  t1.id=t2.id(+) and t1.id(+)=2;
 ID DATA  ID DATA
--- ---- --- ------
  2 2t1    2 2t2
--//结果不对.因为连接在t2表存在(+).使用t1.id(+)=2相当于没有加号,变成t1.id=2;改写如下:

SCOTT@test01p> select * from t1,t2 where  t1.id=t2.id(+) and t2.id(+)=2;
 ID DATA          ID DATA
--- ----- ---------- ------------
  2 2t1            2 2t2
  1 1t1   NULL       NULL
  3 3t1   NULL       NULL

--//OK现在正确了.换一句话讲上面的语句写成如下:

SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id and t2.id=2;
 ID DATA          ID DATA
--- ----- ---------- ----------
  2 2t1            2 2t2
  1 1t1   NULL       NULL
  3 3t1   NULL       NULL

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  bzjahpxh02m63, child number 0
-------------------------------------
select * from t1 left join t2 on t1.id=t2.id and t2.id=2
Plan hash value: 1823443478
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      3 |    42 |     6   (0)| 00:00:01 |  1995K|  1995K|  978K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |     7 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / T1@SEL$1
   3 - SEL$2BFA4EE4 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   3 - filter("T2"."ID"=2)

SCOTT@test01p> @ expand_sql_text12c.sql  bzjahpxh02m63 0
PL/SQL procedure successfully completed.

M_SQL_OUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT "A1"."QCSJ_C000000000300000_0" "QCSJ_C000000000300000","A1"."QCSJ_C000000000300002_1" "QCSJ_C000000000300002","A1"."QCSJ_C000000000300001_2" "QCSJ_C00000
0000300001","A1"."QCSJ_C000000000300003_3" "QCSJ_C000000000300003" FROM  (SELECT "A3"."ID" "QCSJ_C000000000300000_0","A3"."DATA" "QCSJ_C000000000300002_1","A2".
"ID" "QCSJ_C000000000300001_2","A2"."DATA" "QCSJ_C000000000300003_3" FROM "SCOTT"."T1" "A3","SCOTT"."T2" "A2" WHERE "A3"."ID"="A2"."ID" AND "A2"."ID"=2) "A1"
--//转换依旧是错的.

SCOTT@test01p> @ 10053x bzjahpxh02m63 0
PL/SQL procedure successfully completed.

--//检查转储发现,注:sql语句我做了格式化处理.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."DATA" "DATA","T2"."ID" "ID",
       "T2"."DATA" "DATA"
  FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
 WHERE "T1"."ID"    = "T2"."ID"(+)
   AND "T2"."ID"(+) = 2
kkoqbc: optimizing query block SEL$2BFA4EE4 (#0)

--//也就是讲使用ansi语法,这样写不会发生像前面的转换.

4.继续测试:
SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id and t1.data='2t1';
 ID DATA          ID DATA
--- ----- ---------- ------
  2 2t1            2 2t2
  1 1t1   null       null
  3 3t1   null       null

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  5w608wztt55qp, child number 0
-------------------------------------
select * from t1 left join t2 on t1.id=t2.id and t1.data='2t1'
Plan hash value: 1823443478
-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      3 |    42 |     6   (0)| 00:00:01 |  1995K|  1995K|  982K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| T2   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / T1@SEL$1
   3 - SEL$2BFA4EE4 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T1"."DATA"=CASE  WHEN ("T2"."ID" IS NOT NULL) THEN '2t1' ELSE '2t1' END )
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
--//如果写成如上,写成(+)的语法就比较复杂.

SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id and t2.data='2t2';
 ID DATA          ID DATA
--- ----- ---------- -------
  2 2t1            2 2t2
  1 1t1   null       null
  3 3t1   null       null

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  b4xnzyw0sgp11, child number 0
-------------------------------------
select * from t1 left join t2 on t1.id=t2.id and t2.data='2t2'

Plan hash value: 1823443478

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      3 |    42 |     6   (0)| 00:00:01 |  1995K|  1995K|  972K (0)|
|   2 |   TABLE ACCESS FULL| T1   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T2   |      1 |     7 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / T1@SEL$1
   3 - SEL$2BFA4EE4 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   3 - filter("T2"."DATA"='2t2')

5.right join测试看看:
SCOTT@test01p> select * from t1 right join t2 on t1.id=t2.id and t2.id=2;
        ID DATA   ID DATA
---------- --------- -----
         2 2t1     2 2t2
null       null    4 4t2
null       null    3 3t2

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  79u9x9fb2rq37, child number 0
-------------------------------------
select * from t1 right join t2 on t1.id=t2.id and t2.id=2

Plan hash value: 1426054487

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      3 |    42 |     6   (0)| 00:00:01 |  1743K|  1743K|  943K (0)|
|   2 |   TABLE ACCESS FULL| T2   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
|   3 |   TABLE ACCESS FULL| T1   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / T2@SEL$1
   3 - SEL$2BFA4EE4 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID" AND "T2"."ID"=CASE  WHEN ("T1"."ID" IS NOT NULL) THEN 2 ELSE 2 END )

SCOTT@test01p> select * from t1 right join t2 on t1.id=t2.id and t1.id=2;
        ID DATA   ID DATA
---------- ----- --- ------
         2 2t1     2 2t2
null       null    4 4t2
null       null    3 3t2

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  fz7yxaqccfb6z, child number 0
-------------------------------------
select * from t1 right join t2 on t1.id=t2.id and t1.id=2
Plan hash value: 1426054487

-------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |        |       |     6 (100)|          |       |       |          |
|*  1 |  HASH JOIN OUTER   |      |      3 |    42 |     6   (0)| 00:00:01 |  1995K|  1995K|  955K (0)|
|   2 |   TABLE ACCESS FULL| T2   |      3 |    21 |     3   (0)| 00:00:01 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T1   |      1 |     7 |     3   (0)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2BFA4EE4
   2 - SEL$2BFA4EE4 / T2@SEL$1
   3 - SEL$2BFA4EE4 / T1@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."ID"="T2"."ID")
   3 - filter("T1"."ID"=2)

--//总之我个人还是趋向于喜欢(+)的语法.

6.附上测试脚本:
$ cat expand_sql_text12c.sql
variable m_sql_out clob

declare
    m_sql_in  clob ;
begin
        select sql_fulltext into m_sql_in from v$sqlarea where sql_id='&&1';
--        dbms_sql2.expand_sql_text(        -- 11g
--                m_sql_in,
--                :m_sql_out
--        );
        dbms_utility.expand_sql_text(     -- 12c
                m_sql_in,
                :m_sql_out
        );
end;
/
set long 20000
column m_sql_out format a160
print m_sql_out

$ cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'a'||'&&1');

$ cat dpc.sql
set verify off
--select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALLSTATS LAST PEEKED_BINDS &2 cost partition'));
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));
prompt
prompt argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline adaptive
prompt


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

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2703
  • 访问量
    6492836