sql - Force index use in Oracle

Question

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?

Answer 1

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