Home » RDBMS Server » Performance Tuning » Proper index is not used ... (Oracle, 11.2.0.2, Linux 5.5)
Proper index is not used ... [message #568895] Wed, 17 October 2012 07:31 Go to next message
ardhendu
Messages: 4
Registered: October 2012
Location: INDIA
Junior Member
Hi,

I have a table whose size is 2.3 GB and there are two indexes on it. One index is based on a Date column whose size is 900 MB, and the Other index consists of 5 columns including the date column, and the size is almost 2GB. But when i query the table using the Date column, it is doing a range scan on the second index which is almost the same size as the table. My question is, why is it not using the first index ??? What steps should i take so that it uses the First index without passing hints.

Regards,
Re: Proper index is not used ... [message #568897 is a reply to message #568895] Wed, 17 October 2012 07:34 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For any performances question, please read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel
Re: Proper index is not used ... [message #568934 is a reply to message #568897] Wed, 17 October 2012 15:02 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does the query need to go to the table at all? Or are all of the columns used present in the 5-column index? If Oracle does not need to lookup the table, the longer index will be preferred.

Are there any other WHERE clauses? The longer index may be preferred because it is performing additional filtering.

It also might be possible that Oracle is ignoring the smaller index because it is a complete subset of the longer index. The reasoning would be that there is no point using both indexes for different queries - that would be an inefficient use of space in the buffer cache. By making all queries use only the superset index, all queries would get a better cache-hit ratio. Note that this is just an untested theory.

You could use a hint to force using the index, but you may get worse performance on this query, or possibly worse performance on the database overall.

Ross Leishman

Re: Proper index is not used ... [message #568937 is a reply to message #568895] Wed, 17 October 2012 15:08 Go to previous messageGo to next message
John Watson
Messages: 4693
Registered: January 2010
Location: Global Village
Senior Member
Quote:
What steps should i take so that it uses the First index without passing hints.
A clear answer: alter your 5 column index to make it invisible. Then run your query, and see if it os faster or slower.
Re: Proper index is not used ... [message #569062 is a reply to message #568934] Fri, 19 October 2012 05:07 Go to previous message
ardhendu
Messages: 4
Registered: October 2012
Location: INDIA
Junior Member
Thanks Ross,

I got the point. The longer index contains the columns which are in the select query. that is the reason why it was not using the single coulmn index as it was getting all the required data from the index itself.


Thanks everyone for your valuable inputs.
Previous Topic: incremental stats not happening
Next Topic: How Oracle optimizer choose joins (hash, Merge & nested loop join)
Goto Forum:
  


Current Time: Sun Oct 26 03:51:18 CDT 2014

Total time taken to generate the page: 0.06965 seconds