[20200711]關於左右連線的問題.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於mysql連線的問題MySql
- 關於連線池的問題！
- 關於jetty連線關閉問題Jetty
- [20171120]關於find 軟連線問題.txt
- 請問，關於資料庫連線的問題。資料庫
- 關於 Homestead 連線 MySQL 問題MySql
- 關於canonmx538無線連線的問題
- 關於JPetstore連線資料庫問題資料庫
- 請教關於資料庫連線的問題資料庫
- 關於資料庫連線的一些小問題資料庫
- 關於tomcat資料庫連線池的問題Tomcat資料庫
- 關於在執行java連線MongoDB時遇到的連線超時問題JavaMongoDB
- 關於用JB7連線資料庫的問題。資料庫
- 關與連線池的配置問題！
- 求助！關於多執行緒連線資料庫的問題執行緒資料庫
- 請教jdon關於連線池對應的遊標問題
- 請教關於資料庫連線池的設定問題？資料庫
- 一個非常離奇關於LINUX SSH連線的問題Linux
- 關於紅旗linux網路連線時斷時連的問題(轉)Linux
- 關於WebLogic 訪問Oracle 資料庫（建立連線池）問題WebOracle資料庫
- 弱弱的問一個菜鳥問題（關於單態和連線池）
- [20160219]關於連線順序.txt
- 請教關於連線池導至系統出錯的問題
- 請高手幫忙，關於多個連線的hibernate配置問題
- 關於資料庫連線問題的一般解決方法資料庫
- 關於hibernate使用c3p0連線池的問題.
- 資料庫連線未關閉的問題資料庫
- Oracle 左右連線總結Oracle
- oracle 左右連線詳解Oracle
- 關於EJB的學習曲線的問題
- pl/sql dev連線oracle相關問題SQLdevOracle
- CentOS 7遠端連線相關問題CentOS
- 如何解決撥號連線關不掉的問題
- [20181229]關於字串的分配問題.txt字串
- 關於android stdio連線mysql資料庫出現過的問題AndroidMySql資料庫
- [20160330]關於連線順序3.txt
- 關於面向連線與面向無連線
- Oracle左右全連線總結Oracle