This content has been marked as final. Show 5 replies
Oracle by default uses the best index for the query. If you want change it you must use hints (index in this case). Something like this:
select /*index(col2)*/ col1,col2 from employee where col2='ABC'
For more info:
remember close your threads when your question will be solved, marking them as answered
oradba11 Handle: oradba11 Status Level: Newbie (30) Registered: Sep 22, 2008 Total Posts: 500 Total Questions: 168 (110 unresolved) Name OraDba Location U.S.A. Occupation Database Administrator Biography Workning on Oracle Databases,having upto 5 years of experience.
If you hint an optimizer directive and that directive is not followed, then either you've not hinted correctly or the hint cannot be obeyed.
Regarding hinting correctly, it's pretty tricky in recent versions depending on the complexity of the query, see
Check the predicates section for implicit datatype conversions - that's one reason why your index might not be used.
[url https://forums.oracle.com/forums/thread.jspa?threadID=863295]How to post a sql tuning request
oradba11 wrote:It's not a good idea to use a hint in a production environment.
Hi, i am wokring on 126.96.36.199.0 and AIX..
We have a select query with one column in filter clause.
select col1,col2 from employee where col2='ABC' .......
col2 is having index and col1 is primary key and having index.
When we execute this query ...it is always taking primary key index ...not picking index on col2 ....and sql is runing very slow ...
I tried to use hint to use index on col2 but optimizer is using same primary key index...
How can we test by using col2 index and check if sql is performing better then using primary key index ...
Are you saying you have another part of the SQL that you're not showing with col1='DEF'? Then Oracle should choose the PK index on that column.
Why do you think it should choose the index on col2?
It is probably better to post the whole SQL here along with the explain plan.
Edited by: Paul Horth on Feb 8, 2013 2:33 PM
In addition to what the others said:
Is col2 unique? See http://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/
Also see http://richardfoote.wordpress.com/2008/02/13/its-less-efficient-to-have-low-cardinality-leading-columns-in-an-index-right/
Is the optimizer deciding a skip scan of the primary is better than a range scan of the other index?
Also see if putting in some reference to col1 changes things.