Skip to content
Advertisement

Force index use in Oracle

I encountered this question in an interview and had no clue how to answer:

There is a table which has a index on a column, and you query:

select * from table_name where column_having_index="some value";

The query takes too long, and you find out that the index is not being used. If you think the performance of the query will be better using the index, how could you force the query to use the index?

Advertisement

Answer

You can use optimizer hints

select /*+ INDEX(table_name index_name) */ from table etc…

More on using optimizer hints: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm

User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement