[sql] 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?

There could be many reasons for Index not being used. Even after you specify hints, there are chances Oracle optimizer thinks otherwise and decide not to use Index. You need to go through the EXPLAIN PLAN part and see what is the cost of the statement with INDEX and without INDEX.

Assuming the Oracle uses CBO. Most often, if the optimizer thinks the cost is high with INDEX, even though you specify it in hints, the optimizer will ignore and continue for full table scan. Your first action should be checking DBA_INDEXES to know when the statistics are LAST_ANALYZED. If not analyzed, you can set table, index for analyze.

begin 
   DBMS_STATS.GATHER_INDEX_STATS ( OWNNAME=>user
                                 , INDNAME=>IndexName);
end;

For table.

begin 
   DBMS_STATS.GATHER_TABLE_STATS ( OWNNAME=>user
                                 , TABNAME=>TableName);
end;

In extreme cases, you can try setting up the statistics on your own.

You can use:

WITH index = ...

more info

If you think the performance of the query will be better using the index, how could you force the query to use the index?

First you would of course verify that the index gave a better result for returning the complete data set, right?

The index hint is the key here, but the more up to date way of specifying it is with the column naming method rather than the index naming method. In your case you would use:

select /*+ index(table_name (column_having_index)) */ *
from   table_name
where  column_having_index="some value"; 

In more complex cases you might ...

select /*+ index(t (t.column_having_index)) */ *
from   my_owner.table_name t,
       ...
where  t.column_having_index="some value"; 

With regard to composite indexes, I'm not sure that you need to specify all columns, but it seems like a good idea. See the docs here http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#autoId18 on multiple index_specs and use of index_combine for multiple indexes, and here http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements006.htm#BABGFHCH for the specification of multiple columns in the index_spec.

There is an appropriate index on column_having_index, and its use actually increase performance, but Oracle didn't use it...
You should gather statistics on your table to let optimizer see that index access can help. Using direct hint is not a good practice.

I tried many formats, but only that worked:

select /*+INDEX(e,dept_idx)*/ * from emp e;