Force index use in Oracle

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

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.

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_namewhere  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.