[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这类的写法，我估计在学校老师讲课就是使用这样的语法。
一些有一定阅览的程序员才使用+这类语法。
203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
