How Oracle implement ANSI left outer join syntax
At least, there will be two ways to handle left outer join, one is the traditional way to handle the case such as t1.x = t2.x (+), and the new way we saw in last article to handle the new ANSI syntax. So we will begin with such two execution plans.
cbo@ORCL> select * from t1 left outer join t2 on (t1.x = t2.x); Execution Plan ---------------------------------------------------------- Plan hash value: 1823443478 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6 | 78 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 6 | 78 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| T1 | 5 | 30 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T2 | 8 | 56 | 2 (0)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."X"="T2"."X"(+)) cbo@ORCL> select * from t1 left outer join t2 on (t1.x = t2.x and t1.c = 11); Execution Plan ---------------------------------------------------------- Plan hash value: 2394896828 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 225 | 12 (0)| 00:00:01 | | 1 | NESTED LOOPS OUTER | | 5 | 225 | 12 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | T1 | 5 | 30 | 2 (0)| 00:00:01 | | 3 | VIEW | | 1 | 39 | 2 (0)| 00:00:01 | |* 4 | TABLE ACCESS FULL| T2 | 1 | 7 | 2 (0)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("T1"."X"="T2"."X" AND "T1"."C"=11)
The output of the first sample should be like the output of
select * from t1,t2 where t1.x = t2.x(+);
For the second sample, we have no a simple corresponding (+) expression. But from the execution plan, we can guess one possibility, the outline is an left outer join, composite of one table and one view, the view come from t2, and have predicates from t1.c:
select * from t1, (select t2.* from t1, t2 where t1.x = t2.x and c = 11) t2 where t1.x = t2.x(+);
and corresponding execution plan:
Execution Plan ---------------------------------------------------------- Plan hash value: 1247508653 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 225 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN OUTER | | 5 | 225 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL | T1 | 5 | 30 | 2 (0)| 00:00:01 | | 3 | VIEW | | 1 | 39 | 5 (20)| 00:00:01 | |* 4 | HASH JOIN | | 1 | 13 | 5 (20)| 00:00:01 | |* 5 | TABLE ACCESS FULL| T1 | 1 | 6 | 2 (0)| 00:00:01 | | 6 | TABLE ACCESS FULL| T2 | 8 | 56 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."X"="T2"."X"(+)) 4 - access("T1"."X"="T2"."X") 5 - filter("C"=13)
This is complicated than the sample output, what Oracle really does to this type of left outer join can be investigated through the 10053 trace, below is the transformed SQL.
SELECT T1.C C, T1.X X, T1.Y Y, lv.D_0 D, lv.X_1 X, lv.Y_2 Y FROM CBO.T1 T1, LATERAL((SELECT T2.D D_0, T2.X X_1, T2.Y Y_2 FROM CBO.T2 T2 WHERE T1.X = T2.X AND T1.C = 11))(+) lv
Like the way I show above, we can analyze the statement below, and translate them into the (+) way.
select t1.*,t2.* from t1 left outer join t2 on (t1.x=t2.x and (t2.d>23 or t2.d<23));
Another different issue is translating the statement below:
select t1.*,t2.* from t1 left outer join t2 on (t1.x=t2.x or t1.y = t2.y);
Oracle still use the Lateral View to generate the execution plan, but I failed to find an suitable (+) way to solve this problem, what I found is another way:
select * from t1, t2 where t1.x = t2.x or t1.y = t2.y union all select t1.*, null, null, null from t1 where not exists (select 1 from t2 where t2.x = t1.x or t1.y = t2.y);
This method is similar to the issue about “full outer join”.
SELECT T1.c, T2.d FROM T1 FULL OUTER JOIN T2 ON T1.x = T2.y;
will be translated to
SELECT T1.c, T2.d FROM T1, T2 WHERE T1.x = T2.y (+) UNION ALL SELECT NULL, T2.d FROM T2 WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE T1.x = T2.y);
The general rule is that degrade left outer join to equi-join, and degrade full outer join to left outer join.
Reference:
The articles below would be useful to understand this topic:
OuterJoins in Oracle from http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html
ANSI Outer Joins And Lateral Views from http://structureddata.org/2008/02/18/ansi-outer-joins-and-lateral-views/
What’s in a Condition? from http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html