Skip to Main Content
  • Questions
  • Question about Leading Hint and outer Joined tables

Breadcrumb

Question and Answer

Chris Saxon

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

Comments

A reader, November 20, 2023 - 3:36 pm UTC

Thanks Chris for the clarification.

Regards,

Ravi S
Chris Saxon
November 21, 2023 - 10:19 am UTC

You're welcome

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.