Outer joins access tables in following order:
from t1, t2 where t1.col1(+)=t2.col1 –> table will be accessed in order t2, t1
from t1, t2 where t1.col1=t2.col1(+) –> table will be accessed in order t1,t2
One of my developer had to do some data cleanup so he quickly staged data in table t1, t2 was our large fact table and ran following query (test case). Query ran for hours on Exadata and did not finish. ![]()
select /*+ ordered full(t1) full(t2) parallel(t1,4) parallel(t2,4) */ t1.*, t2.* from t1,t2 where t1.col1(+)=t2.col1;
When I looked at SQL monitoring report driving table was t2 (large fact table). Below is the output from test case:
SQL> create table t1(c1, c2)
2 as
3 select level, rpad('x',10)
4 from dual
5 connect by level <= 200000
6 ;
Table created.
SQL>
SQL> create table t2(c1, c2)
2 as
3 select level, rpad('x',10)
4 from dual
5 connect by level <= 100
6 ;
Table created.
SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T1');
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_table_stats(user, 'T2');
PL/SQL procedure successfully completed.
SQL> explain plan for
2 select /*+ ordered */ t2.*,t1.*
3 from t2, t1
4 where t1.c1 = t2.c1(+);
Explained.
SQL>
SQL> @utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1823443478
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 5859K| | 436 (1)| 00:00:06 |
|* 1 | HASH JOIN OUTER | | 200K| 5859K| 5472K| 436 (1)| 00:00:06 |
| 2 | TABLE ACCESS STORAGE FULL| T1 | 200K| 3125K| | 165 (1)| 00:00:02 |
| 3 | TABLE ACCESS STORAGE FULL| T2 | 100 | 1400 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1"(+))
22 rows selected.
SQL> explain plan for
2 select t2.*,t1.*
3 from t2, t1
4 where t1.c1 = t2.c1(+);
Explained.
SQL>
SQL> @utlxpls.sql
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 312430291
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 200K| 5859K| 169 (2)| 00:00:03 |
|* 1 | HASH JOIN RIGHT OUTER | | 200K| 5859K| 169 (2)| 00:00:03 |
| 2 | TABLE ACCESS STORAGE FULL| T2 | 100 | 1400 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| T1 | 200K| 3125K| 165 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."C1"="T2"."C1"(+))
When ORDERED hint was added it used t1 (large table as driving table) with hash join outer and when ORDERED hint was NOT used it used t2 (small table as driving table) with hash join right outer.
Output on left is without ORDERED hint and on right is with ORDERED hint.
So based on above output looks like that when ORDERED hint was used with OUTER JOIN, Oracle optimizer does not go through join permutations (in this particular case swapping join inputs) and hence choose T1 (larger table) as driving table. When ORDERED hint was removed, Oracle optimizer computes cost of swapping join inputs and choose T2 (smaller table) as driving table. Why Oracle considered join order as t1, t2 with ordered hint? I think because of outer join.
For my production issue, I asked ETL/development team to remove ORDERED hint and query ran in 12 minutes.