Oracle hint Leading 的使用

Leading()： 指示Oracle在执行join(hash join, nested loop join, merge join)时的连接顺序。

当执行计划不按照最优的表连接顺序时，考虑使用Leading 改变执行计划。 使用leading 的目的是减少检索数据量， 提高查询性能。

eg:

select v.*, t.* from v_view1 join (select  /*+ leading(t1, tftc) */ * from tftc, t1 where t1.id=tftc.id) t
on v.id=t.id and v.name=t.name left join t2 on t2.name=v.name

;

LEADING Hint

Description of the illustration leading_hint.gif

(See "Specifying a Query Block in a Hint", tablespec::=)

The LEADING hint instructs the optimizer to use the specified set of tables as the prefix in the execution plan. This hint is more versatile than the ORDERED hint. For example:

SELECT /*+ LEADING(e j) */ *
    FROM employees e, departments d, job_history j
    WHERE e.department_id = d.department_id
      AND e.hire_date = j.start_date;

The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.