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;

PARALLEL hint

The PARALLEL hint causes the optimizer to break a query into pieces (the degree of parallelism) and process each piece with a different process. The PARALLEL hint will enable the use of parallel operations. If the degree is not specified with the hint, the default degree specifiedduring the table creation will be used.

Syntax:

select /*+ PARALLEL (table, DEGREE) */ ...

The degree is the number of pieces into which the query is broken.

Example1:

select /*+ PARALLEL (order_line_items) */ invoice_number, invoice_date

from order_line_items

order by invoice_date;

Example2:

select /*+ PARALLEL (order_line_items, 4) */ invoice_number, invoice_date

from order_line_items

order by invoice_date;

This statement does not specify a degree of parallelism. The default degree of parallelism is dictated by the table definition when the table was created.

Example3:

select /*+ PARALLEL (oli, 4) */ invoice_number, invoice_date

from order_line_items oli

order by invoice_date;

ORDERED hint

The ORDERED hint is one of the most powerful hints. It process the table of query in the sequential order listed in the FORM clause.
When the ORDERED hint is not used, Oracle may internally switch the driving table when compared to how tables are listed in the FROM clause (EXPLAIN PLAN can show how tables are accessed).

Syntax:

select /*+ ORDERED */ column1, …

Example1:

select /*+ ORDERED */ empno, ename, dept.deptno

from emp, dept

where emp.deptno = dept.deptno

and dept.deptno = 1

and emp.empno = 7747;

Emp and dept have been analyzed (using the cost-based optimizer) and there are no indexes on both the table, the emp table is accessed first and the dept table is accessed second.

Example2:

select /*+ ORDERED */ 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 = 40;;

INDEX Hint

The INDEX hint is frequently used to force one or more indexes to be executed for a given query. Oracle generally chooses the correct index or indexes with the optimizer, but when the optimizer chooses the wrong index or no index at all, this hint is excellent. You may also use multiple indexes with this hint and Oracle will choose one or more of the indexes specified based on the best plan. If you only specify one index, the optimizer considers only one index.

Syntax:

select /*+ INDEX (table index1, index2…) */ column1, …

from Table_Name

where conditions;

Example1:

select /*+ INDEX (emp deptno_idx) */ empno, ename, deptno
from emp
where deptno = 10;

Example2:

Multiple indexes for a single table can be specified, but it is usually better to specify only the most restrictive index on a given query (avoiding the merging of the result of each index). If multiple indexes are specified, Oracle chooses which (one or more) to use, so be careful or your hint could potentially be overridden.

select /*+ INDEX (emp deptno_idx, empno_idx) */ empno, ename, deptno
from emp
where deptno = 10
and empno = 7750;

Example3:

select /*+ INDEX */ empno, ename, deptno
from emp
where deptno = 1
and empno = 7750;

In this example, no index is specified. Oracle now weighs all of the possible indexes that are available and chooses one or more to be used. Since we have not specified a particular index, but we have specified the INDEX hint, the optimizer will not do a full table scan.

Top Hints Used

• A hint is an instruction to the optimizer which can influence the optimizer to choose a difference plan for a sql statement. The optimizer uses these hints to choose an execution plan for the statement. 
• Hints should be used only as LAST RESORT if statistics were gathered properly and query is still following a sub-optimal execution plan.

DBAs and developers use below hints in their day-to-day tuning
1. INDEX
2. ORDERED
3. PARALLEL
4. FIRST_ROWS
5. FULL
6. LEADING
7. USE_NL
8. APPEND
9. USE_HASH