Why is my query ignoring the Index use?You can use HINT inside a query to force Oracle Engine to use an index. Is this a good idea?
Oracle Syntax: Hint
Oracle Hint forces Index usage with any query. Before using a HINT to your query to force Index use, did you check if your query is written in such a way to use Index? If that is not the case, you need to know why Oracle Engine decided not use an Index? In other word, why is there a full table scan to my query?
Like us, Oracle has its own mind and it makes decision based on cost weather or not to use Index. Sometime full table scan is MUCH faster than Index use. You might be wondering why? If your query is going to return over 10% of the data, it is faster for Oracle to make a full table scan whereas Index scan will be much slower in that case.
Few other reasons on why: Is your table stats current (make sure they are up-to-date!), and the last one is: how unique is your data in your Indexed column? If your indexed column stored Boolean value and you have more ‘T’ than ‘F’, do you expect Oracle to use index when you are querying for T? What happens when you use HINT to that query? Your performance of your query will go down my friend!
The one other reason could be using the wrong Index type. Make sure you are using functional Index properly to match your query. Check everything listed in this short article and you will know why and should be able to make it work if you truly need Index.
Oracle Syntax: Hint
SELECT /*+ INDEX(x emp_idx1) */ ... FROM scott.emp x...
Like us, Oracle has its own mind and it makes decision based on cost weather or not to use Index. Sometime full table scan is MUCH faster than Index use. You might be wondering why? If your query is going to return over 10% of the data, it is faster for Oracle to make a full table scan whereas Index scan will be much slower in that case.
Few other reasons on why: Is your table stats current (make sure they are up-to-date!), and the last one is: how unique is your data in your Indexed column? If your indexed column stored Boolean value and you have more ‘T’ than ‘F’, do you expect Oracle to use index when you are querying for T? What happens when you use HINT to that query? Your performance of your query will go down my friend!
The one other reason could be using the wrong Index type. Make sure you are using functional Index properly to match your query. Check everything listed in this short article and you will know why and should be able to make it work if you truly need Index.
Interested in working with me? I can be reached at pbaniya04[at]gmail.com for any questions, consulting opportunities or you may drop a line to say HELLO. Thank your again for visiting my blog and looking forward to serving you more.
Have a Database-ious Day!
Have a Database-ious Day!
No comments