[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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於 Homestead 連線 MySQL 問題MySql
- 關於canonmx538無線連線的問題
- [20191129]關於hugepages的問題.txt
- [20181229]關於字串的分配問題.txt字串
- [20191202]關於hugepages相關問題.txt
- [20181128]toad連線資料庫的問題.txt資料庫
- [20181123]關於降序索引問題.txt索引
- [20180403]關於時區問題.txt
- 關於在執行java連線MongoDB時遇到的連線超時問題JavaMongoDB
- [20200416]關於軟軟解析的問題.txt
- [20190918]關於函式索引問題.txt函式索引
- [20230317]關於TIME_WAIT問題.txtAI
- [20210520]關於主鍵索引問題.txt索引
- [20211018]運維中關於history的問題.txt運維
- [20181006]12c使用toad連線問題.txt
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- [20181124]關於降序索引問題4.txt索引
- [20181124]關於降序索引問題3.txt索引
- [20181124]關於降序索引問題2.txt索引
- [20190910]關於降序索引問題5.txt索引
- [20211220]關於標量子查詢問題.txt
- [20190102]關於字串的分配問題(10g).txt字串
- [20180419]關於閃回的一些問題.txt
- [20180423]關於rman備份的問題2.txt
- 關於android stdio連線mysql資料庫出現過的問題AndroidMySql資料庫
- [20230306]os認證連線資料庫問題.txt資料庫
- [20191220]關於共享記憶體段相關問題.txt記憶體
- [20221212]關於pre_page_sga引數的問題.txt
- [20190603]關於dbms_output輸出問題.txt
- [20190409]pre_page_sga=true與連線緩慢的問題.txt
- [20230214]資料庫連線訪問asm相關檢視.txt資料庫ASM
- [20220909]bbed關於刪除記錄恢復的問題.txt
- CentOS 7遠端連線相關問題CentOS
- [20180819]關於父子游標問題(11g).txt
- [20190930]關於資料結構設計問題.txt資料結構
- [20191202]關於oracle例項是否使用hugepages問題.txtOracle
- 關於遠端連線虛擬機器Network error: Connection timed out的問題虛擬機Error
- 關於為前端連線資料庫出現問題答案的尋找(404)前端資料庫