Menu
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?
635
As for your question -- there is no way to FORCE the optimizer to use an index, we can make it so that the use of an index is possible.
How the Force Index Works. The force index is calculated by subtracting yesterday's close from today's close and multiplying the result by today's volume. If closing prices are higher today than yesterday, the force is positive. If closing prices are lower than yesterday's, the force is negative.
In case the query optimizer ignores the index, you can use the FORCE INDEX hint to instruct it to use the index instead. In this syntax, you put the FORCE INDEX clause after the FROM clause followed by a list of named indexes that the query optimizer must use.
Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return a small portion of a table's rows. Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries.
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
173
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
