Force oracle to use index

Question

Is there any way to force oracle to use index except Hints?

Answer 1

No. And if the optimizer doesn't use the index, it usually has a good reason for it. Index usage, if the index is poor, can actually slow your queries down.

Answer 2

Oracle doesn't use an index when it thinks the index is

• disabled
• invalid (for example, after a huge data load and the statistics about the index haven't been updated)
• won't help (for example, when there are only two different values in 5 million rows)

So the first thing to check is that the index is enabled, then run the correct GATHER command on your index/table/schema. When that doesn't help, Oracle thinks that loading your index will actually take more time than loading the actual row values. In this case, add more columns to the index to make it appear more "diverse".