LEADING hint

If the query is more complex and it becomes more difficult to figure out the order of all the tables using in ORDER hint. We can figure out the which table access first (driving table), but may not know which table to access after that. The LEADING hint allow us to  specify one table to drive the query while optimizer figure out the rest table.

Syntax:

select /*+ LEADING (table1) */ column1, …

Example1:

select /*+ LEADING(DEPT) */ emp.empno, ename, dept.deptno, itemno

from emp, dept, orders

where emp.deptno = dept.deptno

and emp.empno = orders.empno

and dept.deptno = 1

and emp.empno = 7747

and orders.ordno = 45;