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

User · Accepted Answer

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

User · Answer

I tried many formats  but only that worked  select    INDEX e dept idx      from emp e

User · Answer

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  gt user                                    INDNAME  gt IndexName   end    For table   begin     DBMS STATS GATHER TABLE STATS   OWNNAME  gt user                                    TABNAME  gt TableName   end    In extreme cases  you can try setting up the statistics on your own

User · Answer

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

User · Answer

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

User · Answer

You can use   WITH index         more info

[sql] Force index use in Oracle

Examples related to sql

Examples related to oracle

Examples related to indexing

Examples related to sqlplus