[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這類的寫法，我估計在學校老師講課就是使用這樣的語法。
一些有一定閱覽的程式設計師才使用+這類語法。

[20160213]關於ansi語法.txt

相關文章