Force index use in Oracle

sql oracle indexing sqlplus
Vijay picture Vijay · Dec 3, 2009 · Viewed 222.6k times · Source

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

Rene picture Rene · Dec 3, 2009

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

Related questions

Oracle - Select where field has lowercase characters

I have a table, users, in an Oracle 9.2.0.6 database. Two of the fields are varchar - last_name and first_name. When rows are inserted into this table, the first name and last name fields are supposed to be in …

Read More
Do I need to create indexes on foreign keys on Oracle?

I have a table A and a table B. A has a foreign key to B on B's primary key, B_ID. For some reason (I know there are legitimate reasons) it is not using an index when I join …

Read More
Creating Indexes for Group By Fields?

Do you need to create an index for fields of group by fields in an Oracle database? For example: select * from some_table where field_one is not null and field_two = ? group by field_three, field_four, field_five …

Read More