[20160213]關於ansi語法.txt
[20160213]關於ansi語法.txt
--曾經寫過幾篇關於ansi語法的blog,參考連結:
[20120410]使用ANSI join syntax好嗎？.txt
http://blog.itpub.net/267265/viewspace-720875/
[20150423]left right join.txt
http://blog.itpub.net/267265/viewspace-1593068/
--我以前並不是學習計算機,我一開始學習連線就非常不習慣寫left join,right join的語法.
--oracle sql 語法裡面存在left join,right join連線,而且這種寫法是sql ansi標準.
--我個人工作習慣特別不喜歡這種寫法,使用(+)方式更多一些.
-- 實際上我以前開始學習oracle的時候,對於使用(+)那邊輸出NULL非常混亂.每次都拿scott schema的dept,emp來測試一次.
-- 後來我簡單的記憶是+表示多的意思,也就這個(+)對應的表不存在時輸出NULL值.
--正好春節前<Apress.Expert.Oracle.SQL.Optimization.Deployment.and.Statistics.1430259779.pdf>看完.很明顯作者喜歡使用ansi
--的語法.正好我想起以前最佳化的一個專案,裡面就大量的使用left,right連線的語法,我遇到這個問題我總是腦子轉換成+的寫法.關於這
--些我講講我個人的一些看法:
1.sql ansi語法是標準,自然許多人在用,我看過一些國外的論壇也有一些討論,我認為老外喜歡這種語法,我個人的感覺英文是他們的母語
而使用這種語法非常接近自然語言.而使用+ 我認為跟接近邏輯的表示.
而使用left 相當於在右邊加加號. right 正好相反.
除了一種情況,目前連線不支援兩邊有加號寫法.而使用full join.
2.如果還存在附加條件,使用left,right的語法相對簡單.
--實際上這個問題我第一次遇到實際上開發寫錯.很少遇到,我透過例子在重複說明看看;
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t1 as select rownum id,rownum||'t1' name from dual connect by level<=5;
create table t2 as select rownum+1 id,rownum||'t2' name from dual connect by level<=5;
create unique index i_t1_id on t1(id);
create unique index i_t2_id on t2(id);
--分析表略.
SCOTT@test01p> set null NULL
SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id and t2.id=2;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
2 2t1 2 1t2
5 5t1 NULL NULL
4 4t1 NULL NULL
3 3t1 NULL NULL
1 1t1 NULL NULL
--我想實際的應用很少有查詢這樣的結果,實際上開發的本意是select * from t1 left join t2 on t1.id=t2.id where t2.id=2;
SCOTT@test01p> select * from t1 left join t2 on t1.id=t2.id where t2.id=2;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
2 2t1 2 1t2
--再會過頭看上面的執行計劃:
Plan hash value: 2681112282
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 4 (100)| | 5 |00:00:00.01 | 5 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 5 | 70 | 4 (0)| 00:00:01 | 5 |00:00:00.01 | 5 | 1645K| 1645K| 1043K (0)|
| 2 | TABLE ACCESS FULL | T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 7 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX UNIQUE SCAN | I_T2_ID | 1 | 1 | | 0 (0)| | 1 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$0E991E55
2 - SEL$0E991E55 / T1@SEL$1
3 - SEL$0E991E55 / T2@SEL$1
4 - SEL$0E991E55 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T2"."ID")
4 - access("T2"."ID"=2)
Note
-----
- this is an adaptive plan
D:\tools\sqllaji>cat 10053x.sql
execute dbms_sqldiag.dump_trace(p_sql_id=>'&1',p_child_number=>&2,p_component=>'Compiler',p_file_id=>'&&1');
SCOTT@test01p> @10053x bzjahpxh02m63 0
PL/SQL procedure successfully completed.
--檢視轉儲檔案:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."NAME" "NAME","T2"."ID" "ID","T2"."NAME" "NAME"
FROM "SCOTT"."T1" "T1","SCOTT"."T2" "T2"
WHERE "T1"."ID" = "T2"."ID"(+)
AND "T2"."ID"(+) = 2;
--相當於執行如下：
select * from t1,t2 where t1.id= t2.id(+) and t2.id(+) = 2;
--從這裡可以在oracle內部實際上最終轉化為使用+的格式。
3.再來看看另外一種查詢情況：
--僅僅把後面的查詢條件t2.id=2 換成 t1.id=2. 上班環境變成11G，繼續測試。
select * from t1 left join t2 on t1.id=t2.id where t1.id=2;
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 | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 5 |00:00:00.01 | 5 | | | |
|* 1 | HASH JOIN OUTER | | 1 | 5 | 70 | 6 (0)| 00:00:01 | 5 |00:00:00.01 | 5 | 1645K| 1645K| 897K (0)|
| 2 | TABLE ACCESS FULL| T1 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS FULL| T2 | 1 | 5 | 35 | 3 (0)| 00:00:01 | 5 |00:00:00.01 | 3 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$9E43CB6E
2 - SEL$9E43CB6E / T1@SEL$2
3 - SEL$9E43CB6E / 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 )
--注意我僅僅修改查詢條件：t2.id=2 換成 t1.id=2。這個時候建立在t1.id上的索引就沒有用，變成了全表掃描。
SCOTT@book> @&r/10053x 5katgcygcphpc 0
BEGIN dbms_sqldiag.dump_trace(p_sql_id=>'5katgcygcphpc',p_child_number=>0,p_component=>'Compiler',p_file_id=>'5katgcygcphpc'); END;
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 206
ORA-06512: at "SYS.DBMS_SQLDIAG", line 1185
ORA-06512: at line 1
--奇怪使用dbms_sqldiag.dump_trace會報錯。先放棄，使用10053跟蹤看看:
SCOTT@book> @ &r/10053on 12
Session altered.
SCOTT@book> Select * from t1 left join t2 on t1.id=t2.id and t1.id=2;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
2 2t1 2 1t2
4 4t1
3 3t1
1 1t1
5 5t1
--注意要產生1次硬分析，我修改select的第一個字母大寫。
SCOTT@book> @ &r/10053off
Session altered.
--檢查轉儲跟蹤檔案：
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."NAME" "NAME","T2"."ID" "ID","T2"."NAME" "NAME"
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
--從這些資訊可以發現在oracle內部實際上是轉化為+的語法。
4.再在10g下測試看看：
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
--建表分析略。
SCOTT@test> @ &r/10053on 12
Session altered.
SCOTT@test> Select * from t1 left join t2 on t1.id=t2.id and t1.id=2;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
1 1t1
2 2t1 2 1t2
3 3t1
4 4t1
5 5t1
SCOTT@test> @ &r/10053off
Session altered.
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6cmyybkz1mmna, child number 0
-------------------------------------
Select * from t1 left join t2 on t1.id=t2.id and t1.id=2
Plan hash value: 2158509815
-------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 5 | 215 | 8 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | T1 | 5 | 35 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 36 | 1 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | I_T2_ID | 1 | | 0 (0)| |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$64EAE176
2 - SEL$64EAE176 / T1@SEL$2
3 - SEL$1 / from$_subquery$_004@SEL$2
4 - SEL$1
5 - SEL$1 / T2@SEL$1
6 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("T1"."ID"=2)
6 - access("T2"."ID"=2)
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
--10g下可以使用t2.id的索引。檢視轉儲檔案：(顯示的方便我做了格式化處理)
******* UNPARSED QUERY IS *******
SELECT "T1"."ID" "ID","T1"."NAME" "NAME","
FROM $_subquery$_004"."ID" "ID","
FROM $_subquery$_004"."NAME" "NAME"
FROM "SCOTT"."T1" "T1", LATERAL( (
SELECT "T2"."ID" "ID","T2"."NAME" "NAME"
FROM "SCOTT"."T2" "T2"
WHERE "T1"."ID" = 2
AND "T2"."ID" = 2))(+) "FROM $_subquery$_004";
--奇怪10g就出現LATERAL語法，不過這裡顯示的是UNPARSED QUERY IS。看不到轉化後的sql語句
--在10g如果你執行如下：
SELECT "T1"."ID" "ID","T1"."NAME" "NAME","T2"."ID" "ID","T2"."NAME" "NAME"
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;
ID NAME ID NAME
---------- -------------------- ---------- --------------------
2 2t1 2 1t2
--僅僅輸出1行，與前面不符合。執行計劃如下：
Plan hash value: 1933428060
-----------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 1 | 14 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_T1_ID | 1 | | 0 (0)| |
| 4 | TABLE ACCESS BY INDEX ROWID| T2 | 5 | 35 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | I_T2_ID | 1 | | 0 (0)| |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
3 - SEL$1 / T1@SEL$1
4 - SEL$1 / T2@SEL$1
5 - SEL$1 / T2@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T1"."ID"=2)
5 - access("T1"."ID"="T2"."ID")
5.從以上的分析，我個人的感覺應該在開發內部同一程式設計風格，我更傾向於使用+的方式。
而且我發現剛剛畢業好像更喜歡使用left，right這類的寫法，我估計在學校老師講課就是使用這樣的語法。
一些有一定閱覽的程式設計師才使用+這類語法。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-1988395/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20180912]關於ANSI joins語法.txt
- [20160213]閉包傳遞5.txt
- 關於使用多表做update的語法
- 關於PHP的語法介紹，新手必看PHP
- 關於內表釋放的相關語法對比[1]
- 關於內表釋放的相關語法對比[2]
- 【Vue3.0】關於 script setup 語法糖的用法Vue
- 關於優化器提示使用逗號的語法優化
- 關於ES6、ES7的常用語法整理
- [20111229]date time timestamp? ansi liternals.txt
- 轉 關於shell中if 語法結構的廣泛誤解
- CMakeLists.txt 語法介紹與例項演練
- [20230905]奇怪的語法.txt
- [20160809]exp語法問題.txt
- [20230207]ANSI DATE and TIMESTAMP SQL syntax.txtSQL
- 有關於三個表格結結合查詢的MYSQL語法MySql
- Python相關語法2Python
- 《JavaScript 闖關記》之語法JavaScript
- 關於動態語句
- 關於語言的思考
- [20180208]ezconnect語法.txt
- [20160803]exp/imp語法問題.txt
- [2016026]12c lateral語法.txt
- 我們是不是太關注於語法而忽略了演算法演算法
- 關於動態規劃法動態規劃
- 關於auto increment的寫法REM
- [20171203]關於raw型別.txt型別
- ES6的相關語法
- Oracle多表關聯更新的語法Oracle
- 如何在不開啟txt檔案的情況下，修改utf-8為ansi格式
- 關於大數（C語言）C語言
- 關於C語言的常量C語言
- [20170406]關於檔案頭轉儲.txt
- [20170926]關於linux重定向.txtLinux
- [20171201]關於explain plan.txtAI
- [20171115]關於namespace.txtnamespace
- [20170724]關於sql_id那些事.txtSQL
- [20120903]關於Virtual index.txtIndex