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)