[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2703864/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261105]测试21c SQL Translation Framework.txt
- [20261106]ANSI DATE and TIMESTAMP SQL syntax.txt
- [20261106]光标跟踪事件11.txt
- [20261102]光标跟踪事件9.txt
- [20261102]光标跟踪事件8.txt
- [20261102]为什么出现光标出现TOP_LEVEL_RPI_CURSOR不共享.txt
- [20261102]改变pdb名字.txt
- [20261018]光标跟踪事件3.txt
- [20261016]光标跟踪事件2.txt
- [20261015]模拟kksfbc child completion等待事件2.txt
