Given this below query and evaluating the predicate as such - it conveys an equi-join between T2 and T3 and an Outer join between T1 and T2.
select *
from tbl_2 t2,
tbl_3 t3,
tbl_1 t1
where t2.col1 = t3.col1
and t1.col1 = t2.col1(+)
order by t1.col1, t2.col1, t3.col1;
but Explain plan shows this.
demo@ORA11G> select *
2 from tbl_2 t2,
3 tbl_3 t3,
4 tbl_1 t1
5 where t2.col1 = t3.col1
6 and t1.col1 = t2.col1(+)
7 order by t1.col1, t2.col1, t3.col1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2072707941
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 108M| | 213K (1)| 00:00:41 |
| 1 | SORT ORDER BY | | 1000K| 108M| 120M| 213K (1)| 00:00:41 |
|* 2 | HASH JOIN | | 1000K| 108M| 47M| 178K (1)| 00:00:35 |
| 3 | TABLE ACCESS FULL | TBL_1 | 1000K| 36M| | 50354 (1)| 00:00:10 |
|* 4 | HASH JOIN | | 1000K| 72M| 47M| 112K (1)| 00:00:22 |
| 5 | TABLE ACCESS FULL| TBL_2 | 1000K| 36M| | 50354 (1)| 00:00:10 |
| 6 | TABLE ACCESS FULL| TBL_3 | 1000K| 36M| | 50354 (1)| 00:00:10 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."COL1"="T2"."COL1")
4 - access("T2"."COL1"="T3"."COL1")
No more "Outer joins" spotted in the Explain plan - the 10053 confirm the same.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T2"."COL1" "COL1",
"T2"."COL2" "COL2",
"T3"."COL1" "COL1",
"T3"."COL2" "COL2",
"T1"."COL1" "COL1",
"T1"."COL2" "COL2"
FROM "DEMO"."TBL_2" "T2",
"DEMO"."TBL_3" "T3",
"DEMO"."TBL_1" "T1"
WHERE "T2"."COL1"="T3"."COL1"
AND "T1"."COL1" ="T2"."COL1"
ORDER BY "T1"."COL1",
"T2"."COL1",
"T3"."COL1"
So the parameter that transforms the "Outer join" into an "Equi Join" is this "_optimizer_outer_join_to_inner" (by default set to TRUE)
When this parameter (_optimizer_outer_join_to_inner) is set to FALSE - the optimizer doesn't perform the "Outer join" to "Equi join" transformation.
demo@ORA11G> alter session set "_optimizer_outer_join_to_inner" =false;
Session altered.
demo@ORA11G> set autotrace traceonly explain
demo@ORA11G> select *
2 from tbl_2 t2,
3 tbl_3 t3,
4 tbl_1 t1
5 where t2.col1 = t3.col1
6 and t1.col1 = t2.col1(+)
7 order by t1.col1, t2.col1, t3.col1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3283048800
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 108M| | 213K (1)| 00:00:41 |
| 1 | SORT ORDER BY | | 1000K| 108M| 120M| 213K (1)| 00:00:41 |
|* 2 | HASH JOIN | | 1000K| 108M| 47M| 178K (1)| 00:00:35 |
| 3 | TABLE ACCESS FULL | TBL_3 | 1000K| 36M| | 50354 (1)| 00:00:10 |
|* 4 | HASH JOIN OUTER | | 1000K| 72M| 47M| 112K (1)| 00:00:22 |
| 5 | TABLE ACCESS FULL| TBL_1 | 1000K| 36M| | 50354 (1)| 00:00:10 |
| 6 | TABLE ACCESS FULL| TBL_2 | 1000K| 36M| | 50354 (1)| 00:00:10 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."COL1"="T3"."COL1")
4 - access("T1"."COL1"="T2"."COL1"(+))
the 10053 Trace confirms the same.
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "T2"."COL1" "COL1",
"T2"."COL2" "COL2",
"T3"."COL1" "COL1",
"T3"."COL2" "COL2",
"T1"."COL1" "COL1",
"T1"."COL2" "COL2"
FROM "DEMO"."TBL_2" "T2",
"DEMO"."TBL_3" "T3",
"DEMO"."TBL_1" "T1"
WHERE "T2"."COL1"="T3"."COL1"
AND "T1"."COL1" ="T2"."COL1"(+)
ORDER BY "T1"."COL1",
"T2"."COL1",
"T3"."COL1"
Could you help us to understand
a) Why the optimizer transforms the "outer join" into an "Inner join" - by default ? what was the reason behind this transformation ?