Oracle 左外连接的一些测试
来源:互联网 发布:移动互联网数据报告 编辑:程序博客网 时间:2024/04/30 14:40
为了更加深入左外连接,我们做一些测试,外连接的写法有几种形式,我们可以通过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";
0 0
- Oracle 左外连接的一些测试
- ORACLE的左、右连接测试
- oracle下的左外连接
- Oracle 左连接、左外连接,右连接…
- Oracle的左连接和右连接
- Oracle的左连接与右连接
- Oracle的左连接和右连接
- Oracle的左连接和右连接
- Oracle的左连接和右连接
- Oracle的左连接和右连接
- Oracle的左连接和右连接
- oracle的左连接和右连接
- Oracle的左连接和右连接
- oracle的左连接或右连接
- Oracle的左连接和右连接
- Oracle的左连接右连接
- Oracle的左连接、右连接、(+)
- Oracle 的四种连接-左外连接、右外连接、内连接、全外连接
- 好东西,大家一起分享呀,送福利了,喜欢编程的小伙伴
- 网站长尾词优化排名,你知道怎么做最有效吗?
- CentOS上编译安装OpenCV-2.3.1与ffmpeg-2.1.2
- [LeetCode] Minimum Window Substring
- Maximum repetition substring+POj+后缀数组之求重复次数最多的连续重复子串
- Oracle 左外连接的一些测试
- Ejabberd作为推送服务的优化手段
- OpenCV基础篇之读取显示图片
- css背景图片拉伸填充避免重复显示
- ZoneGridVIew 自定义放大GridView
- Neutron L3 auto Reschedule VRouter feature
- 一些优秀的移动开发网址
- opecvdll 的测试 代码
- spring 错误