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

来源：这里教程网时间：2026-03-03 15:58:03 作者：

[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