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