In order to further the left outer connection, we do some tests, and the outer joins are written in several forms, and we can trace to the final SQL conversion form by 10053.
--Initialize data
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
--use 10053 to find the final converted 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
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
3 30
--final Query after transformations:
Select "A". " ID "" id "," A "." "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
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
3 30
--final Query after transformations:
Select "A". " ID "" id "," A "." "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
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
3 30
--final Query after transformations:
Select "A". " ID "" id "," A "." "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
--In this form you can see that the external connection fails, and the CBO is still very smart
SELECT * from A LEFT join B on a.id = b.id where B.age > 5;
ID Age ID
---------- ---------- ---------- ----------
1 10 1 10
2 20 2 20
--final Query after transformations:
Select "A". " ID "" id "," A "." "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 ";
Some tests for Oracle left outer join
