Oracle tuning: Using Hints

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..