Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.2K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 402 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Forcing index usage without hints

712014
Member Posts: 13
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
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:
Answers
-
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 -
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 -
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???????? -
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 -
You could try adjusting the parameter optimizer_index_cost_adj to a value lower than what it is. As 1 way.
-
Forcing index usage without hintsCheck the SQL Plan Management.
-
But limitattion is that he canot touch the query or code because it is directly fired by an application whose code we cannot changeAlso by using dbms_advanced_rewrite you could apply hints to a rewritten bit of SQL without touching the original.
This discussion has been closed.