Thanks for the question, Ravi.
Asked: November  08, 2023 - 9:07 pm UTC
Last updated: November  21, 2023 - 10:19 am UTC
Version: 19c
Viewed 1000+ times
 
 
You Asked 
Hi Tom,
I have 5 tables in a query, Tab1, Tab2, Tab3, Tab4 and Tab5 and I have the following query below.
Select  *
  From Tab1, Tab2, Tab3, Tab4, Tab5
 Where Tab1.x = Tab2.y(+)
   and Tab1.x = Tab3.y
   and Tab3.x = Tab4.y(+)
   and Tab3.x = Tab5.y;
Question 1. Can a table who's outer joined (in this case Tab2, Tab4) be a Driving table?
Question 2. In the Join Order what happens to tables that are outer joined (in this case Tab2, Tab4). Do we only use tables that are inner joined in the Leading hint?
Regards,
Ravi 
 
and Chris said...
Can a table who's outer joined (in this case Tab2, Tab4) be a Driving table?Yes. The optimizer can use a "RIGHT OUTER" join. This makes the outer joined table the driving table, for example:
create table t10 ( id primary key, stuff ) as 
  select level, rpad ( 'stuff', 100, 'f' ) from dual
  connect by level <= 10;
create table t10000 ( id primary key, stuff ) as 
  select level, rpad ( 'stuff', 100, 'f' ) from dual
  connect by level <= 10000;
set serveroutput off  
set feed only
select count (*), count ( t10.stuff ) 
from   t10000
left join t10
using ( id );
set feed on
select * 
from   dbms_xplan.display_cursor(null, null, 'BASIC');
/*
-----------------------------------------------
| Id  | Operation              | Name         |
-----------------------------------------------
|   0 | SELECT STATEMENT       |              |
|   1 |  SORT AGGREGATE        |              |
|   2 |   HASH JOIN RIGHT OUTER|              |
|   3 |    TABLE ACCESS FULL   | T10          |
|   4 |    INDEX FAST FULL SCAN| SYS_C0011794 |
-----------------------------------------------
*/
In the Join Order what happens to tables that are outer joined (in this case Tab2, Tab4). Do we only use tables that are inner joined in the Leading hint?Leading specifies the order in which you want the optimizer to access the tables from first to last. If you want to read the outer joined tables first, you need to list them first. 
But really you should NOT use this anyway!
In most cases the optimizer will find a good join order - provided the table stats are up-to-date. If you feel the need to "hint" a plan, you should be looking at using SQL Plan Management.  
 
Rating
  (1 rating)
Is this answer out of date? If it is, please let us know via a Comment