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找到最终转换后的SQLalter 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";
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址