Some tests of Oracle left outer connection

In order to further the left outer connection, we do some testing, the wording of the outer join has several forms, we can trace through 10053 to finally the form of SQL conversion.

--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 finally 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 of Oracle left outer connection