Search notes:

Oracle hint: LEADING

The leading hints is used to specify the order in which tables are accessed when joining them.

In order to demonstrate this hint, we create five tables …

create table tq84_tab_a (id number, val varchar2(10));
create table tq84_tab_b (id number, val varchar2(10));
create table tq84_tab_c (id number, val varchar2(10));
create table tq84_tab_d (id number, val varchar2(10));
create table tq84_tab_e (id number, val varchar2(10));

… and use the leading hint in a select statement joining these tables:

explain plan for
select
/*+
   leading(c e d a b)
*/
   a.val   val_a,
   b.val   val_b,
   c.val   val_c,
   d.val   val_d,
   e.val   val_e
from
   tq84_tab_a a                      join
   tq84_tab_b b on a.id = b.id       join
   tq84_tab_c c on a.id = c.id       join
   tq84_tab_d d on a.id = d.id       join
   tq84_tab_e e on a.id = e.id
;

The statement plan shows that the tables are accessed in the order as specfified with the hint:

select * from table(dbms_xplan.display(format=>'basic'));
-- ----------------------------------------------
-- | Id  | Operation               | Name       |
-- ----------------------------------------------
-- |   0 | SELECT STATEMENT        |            |
-- |   1 |  HASH JOIN              |            |
-- |   2 |   HASH JOIN             |            |
-- |   3 |    MERGE JOIN CARTESIAN |            |
-- |   4 |     MERGE JOIN CARTESIAN|            |
-- |   5 |      TABLE ACCESS FULL  | TQ84_TAB_C |
-- |   6 |      BUFFER SORT        |            |
-- |   7 |       TABLE ACCESS FULL | TQ84_TAB_E |
-- |   8 |     BUFFER SORT         |            |
-- |   9 |      TABLE ACCESS FULL  | TQ84_TAB_D |
-- |  10 |    TABLE ACCESS FULL    | TQ84_TAB_A |
-- |  11 |   TABLE ACCESS FULL     | TQ84_TAB_B |
-- ----------------------------------------------

Cleaning up;

drop table tq84_tab_e;
drop table tq84_tab_d;
drop table tq84_tab_c;
drop table tq84_tab_b;
drop table tq84_tab_a;

Oracle hint: LEADING

See also