Forum Stats

  • 3,817,447 Users
  • 2,259,334 Discussions
  • 7,893,776 Comments

Discussions

Categories

Forcing index usage without hints

712014
712014 Member Posts: 13
edited Dec 2, 2009 12:14PM in SQL & PL/SQL
Dear Everybody,

I am a newbie in the world of oracle.

Recently my Boss asked me one question regarding index usage.

He want to force the index to be used though the optimizer does not choose to use it.

But limitattion is that he canot touch the query or code because it is directly fired by an application whose code we cannot change.

So how can one force a query to use index without giving any hints.

It should be for particular query, it should not apply for all the indexes in the database.

I know one parameter "OPTIMIZER_INDEX_COST_ADJ". If you set this parameter properly you can force the optimizer to use the index.But it has its own limitations.

So i want an alternative to this option which can be applied only for a particular query without using hints.

As this is my first thread, sorry if i had made any mistake in posting the question


Thank you all in advance

Regards,
Navin Bandi
Tagged:
· Share on TwitterShare on Facebook

Answers

  • 730428
    730428 Member Posts: 2,087
    Without hints and without modifying the query you cannot force the use of an index.
    The best choice is analyzing the tables involved in the query and let the cost optimizer decide..

    Max
    · Share on TwitterShare on Facebook
  • Himanshu Kandpal
    Himanshu Kandpal Member Posts: 1,971 Silver Badge
    Hi,

    Is there a reason why you want the query to use index? It is not alway true that a index scan is efficient than a full table scan. Some times a full table scan is better that a index scan. Could you please send us the query and the explain plan.

    http://www.oracle.com/technology/oramag/oracle/09-mar/o29asktom.html

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1277166300346684168


    thanks
    · Share on TwitterShare on Facebook
  • 712014
    712014 Member Posts: 13
    Thnx Max for replying to my question.

    What you have said is absolutely right, but sometimes optimizer also predicts poorly about an index usage in a particular query.In that case i have a choice of using hints, but is there anyway without touching the query can i force the optimizer to use the index????????
    · Share on TwitterShare on Facebook
  • 712014
    712014 Member Posts: 13
    edited Dec 2, 2009 7:03AM
    Hi Himanshu,

    Actually we were finding out the alternatives before firing a query, but luckily the optimizer used the index.

    But my is boss still asking me the same question, may be he knows the answer and trying to dig up my knowledge.

    I know concept behind Full table Scan and Index Scan. But in rare case it happens so that optimizer poorly judge the index use.

    Edited by: user10304336 on Dec 2, 2009 5:32 PM
    · Share on TwitterShare on Facebook
  • 732814
    732814 Member Posts: 40
    You could try adjusting the parameter optimizer_index_cost_adj to a value lower than what it is. As 1 way.
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    Forcing index usage without hints
    Check the SQL Plan Management.
    · Share on TwitterShare on Facebook
  • Dom Brooks
    Dom Brooks Member Posts: 5,558 Silver Crown
    edited Dec 2, 2009 12:14PM
    But limitattion is that he canot touch the query or code because it is directly fired by an application whose code we cannot change
    Also by using dbms_advanced_rewrite you could apply hints to a rewritten bit of SQL without touching the original.
    · Share on TwitterShare on Facebook
This discussion has been closed.