Is there any way to force oracle to use index except Hints?
+5
A:
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.
Donnie
2009-11-29 16:22:32
Donnie,So what are the ways to force oracle to use index?
P Sharma
2009-11-29 16:49:59
You can't. That's why I said "No". If you think the index is good enough that it should be being used, see @Arron's answer. Beyond that, if the hint doesn't make it use it, then the index *isn't worth using*.
Donnie
2009-11-29 17:59:54
A:
If your query doesn't include the indexed field in its conditions, then the DB would be foolish to use the index. Thus, I second Donnie's answer.
Carl Smotricz
2009-11-29 16:24:14
It could be a covering index though, or it could be used to avoid a sort due to an ORDER BY. So it's not just a matter of the conditions.
David Aldridge
2009-11-30 10:26:26
+4
A:
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".
Aaron Digulla
2009-11-29 16:30:04
The gather commands are stored procedures. See the documentation of your DB tool how to invoke them. From JDBC, try `call dbms.dbms_stats.gather_table_stats('user', 'table')`. From sql*plus, try `exec dbms....`
Aaron Digulla
2009-11-29 16:42:31
Hey Degulla,I am interesting to know only what is the basic use of the GATHER and ANALYSE commands, and how it will be useful to force index on a table?
P Sharma
2009-11-29 16:49:07
It doesn't *force* Oracle to use the index. But when there isn't an index and you are sure it should use it, it's often because the statistics are out of date. Oracle does update the stats after changes to your tables but only once a days (or what your admins configured). So after many changes, Oracle will think that the index isn't good anymore and will stop using it. Then, you can run the gather commands manually to update the stats and the index will become "good" again.
Aaron Digulla
2009-11-30 08:30:55
+1
A:
You might take a look at oracle stored outlines. You can take an existing query and create a stored outline and tweak the query just like hints. It is just very hard to use. Do some research before you decide to implement stored outlines.
You can add hints into the query that will cause it to look more favorably on one index over another index.
In general if you have collected good statistics on all the tables and indexes Oracle usually implements very good execution plans.
Philip Schlump
2009-11-29 18:29:24
A:
Yes, technically, you can force Oracle to use an index (without hints), in one scenario: if the table is an index-organized table, then logically the only way to query the table is via its index because there is no table to query.
Jeffrey Kemp
2009-12-01 04:19:27