Hackerdude

Oracle tuning: Using Hints

Jun 20

David MartinezCBBTR, SQL Comments Off

Every once in a while you will get a query that no matter what you try, it doesn't use the indices. This is typically because the cost-based optimizer decides that an approach that is slower has a total cost that is lower than using the index and making it faster.

To solve this, you would use a hint. You can find an article on how to do this on dbasupport. But the basics are: The way to use a hint is as follows:

 
SELECT  /*+ INDEX(a, MY_INDEX1) */
  *
FROM
  MY_TABLE a
WHERE
  FUNKY_ID= '3455'
 

You can use Explain Plan to verify that the index was used. Typical problems include:

• Check the spaces. Plus sign must be next to the comment opening. No spaces in between.
• Make sure the table or alias is correct. When in doubt, use an alias.
• Note the comma and space betwen alias and index name

Hints are not very well documented. Google around for oracle hints and you will find some interesting articles however..