Oracle 左外连接的一些測试
为了更加深入左外连接,我们做一些測试,外连接的写法有几种形式,我们能够通过10053跟踪到终于SQL转换的形式。
--初始化数据
create table A
(
id number,
age number
);
create table b
(
id number,
age number
);
insert into A values(1,10);
insert into A values(2,20);
insert into A values(3,30);
insert into B values(1,10);
insert into B values(2,20);
commit;
--用10053找到终于转换后的SQL
alter session set session_cached_cursors =0;
alter session set events '10053 trace name context forever, level 1';
explain plan for select * from A left join B on A.id = B.id and A.age > 5;
explain plan for select * from A left join B on A.id = B.id WHERE A.age > 5;
explain plan for select * from A left join B on A.id = B.id and b.age > 5;
explain plan for select * from A left join B on A.id = B.id where b.age > 5;
alter session set events '10053 trace name context off' ;
select * from A left join B on A.id = B.id and A.age > 5;
ID AGE ID AGE
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
3 30
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
WHERE "A"."ID" = "B"."ID"(+)
AND "A"."AGE" > CASE WHEN("B"."ID"(+) IS NOT NULL) THEN 5 ELSE 5 END
select * from A left join B on A.id = B.id WHERE A.age > 5;
ID AGE ID AGE
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
3 30
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
WHERE "A"."AGE" > 5
AND "A"."ID" = "B"."ID"(+);
select * from A left join B on A.id = B.id and b.age > 5;
ID AGE ID AGE
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
3 30
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
WHERE "A"."ID" = "B"."ID"(+)
AND "B"."AGE"(+) > 5
--这样的形式你能够看到外连接失效,CBO还是很聪明的
select * from A left join B on A.id = B.id where b.age > 5;
ID AGE ID AGE
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
--Final query after transformations:
SELECT "A"."ID" "ID", "A"."AGE" "AGE", "B"."ID" "ID", "B"."AGE" "AGE"
FROM "GG_TEST"."A" "A", "GG_TEST"."B" "B"
WHERE "B"."AGE" > 5
AND "A"."ID" = "B"."ID";
最新文章
- NIO 连接
- for循环练习
- Scala 深入浅出实战经典 第58讲:Scala中Abstract Types实战详解
- 框架基础——全面解析Java注解
- Android 使用Telephony API
- centos使用更新更快的yum源
- Map/Reduce中Join查询实现
- php 文件上传的基本方法
- mysql 导出,导入数据
- 什么是JSONP以及它是怎么产生的
- Microsoft Flow 概览
- Cas 服务器 Service(Cas客户端)注册信息维护
- 【BZOJ5212】[ZJOI2018]历史(Link-Cut Tree)
- python3+scrapy 趣头条爬虫实例
- vue axios全攻略
- 还在为工作发愁?学JavaScript吧
- 补交第二周作业:学习ka li
- sourcetree回退已推送的代码
- 漫画:高并发下的HashMap
- javascript提取联通个人信息和通话记录的代码