Why your hint is Ignored ? Know the correct Usage of Hints

Usually a hint is specified as  a comment to the sql statement. But the job of Oracle optimizer is to differentiate between a hint and a comment. If the Oracle hint is not specified correctly in  an sql statement, there is 100% chance of your hint being ignored by the Oracle optimizer. If an incorrect hint is specified or a hint is mispelled, Optimizer ignores the hint instead of specifying an error. As the name specifies it is a “hint” to OPtimizer to choose the best path, not to report an error because of wrong hint.

I will list some of the cases where people go wrong in specifying a hint

Table aliases :

Let us have a look at the below query

SELECT /*+ INDEX(emp emp_PK) */ emp_name
FROM emp a
WHERE a.emp_no = 32322

In the above query you are instructing the oracle optimizer to use index hint. But Oracle definitely ignores your specification, it may or may not use the index based on the statistics available on the table EMP.This means that if the table is referenced via an alias within the SQL statement, the hint must also reference it via the alias .

Correct Placement of Hints:

Always Place an hint immediately after the first sql verb. if you place the oracle hint anywhere else your hint will be ignored.

Let us see the following query

SELECT emp_name
FROM /*+ INDEX(a emp_pk) */ emp a

Oracle does not report an error, but will definitely ignore your specification of hint.

The correct way of using a hint is

SELECT /*+ INDEX(a emp_pk) */ emp_name
FROM EMP a

INSERT /*+ APPEND */

First three consecutive letters of  your hint should be “/” ,”*”,”+” and the last two letters should be “*”, “/”.