| Proper index is not used ... [message #568895] |
Wed, 17 October 2012 07:31  |
 |
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 #568934 is a reply to message #568897] |
Wed, 17 October 2012 15:02  |
rleishman
Messages: 3728
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 |
John Watson
Messages: 8960
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 |
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
