Home » SQL & PL/SQL » SQL & PL/SQL » Composite index usage
Composite index usage [message #187261] Fri, 11 August 2006 09:21 Go to next message
Messages: 2
Registered: April 2006
Junior Member

I have an existing table with lot of composite indexes. Let me explain like this.( say columns are a, b, c, d,e)

The composite indexes are like below:
1) On a,b,c,d,e
2) On a,b,c,d
3) On a,d,e
4) On a,c,e
5) On a,c,d,e
6) a,b,c,e

This is an existing application. My question is that do I need these many composite indexes? With new skip scan Oracle feature, wouldn't just one composite index on a,b,c,d,e suffice?

Please let me know your suggestions

Re: Composite index usage [message #187263 is a reply to message #187261] Fri, 11 August 2006 09:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It depends on data.
Please search the forum. You will find a lot of discussions.
Re: Composite index usage [message #187265 is a reply to message #187261] Fri, 11 August 2006 09:42 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Index 2 is definitely superfluous, as it is the same as index 1, but with one less column.

AskTom has a good discussion on this.


it works well if and only if the leading edge of the index (a in the example
above) has very few discrete values and the optimizer understands that. For
example and index on (GENDER,EMPNO) where gender has the values 'M', 'F' and
EMPNO is unique.
Re: Composite index usage [message #187337 is a reply to message #187261] Sat, 12 August 2006 04:57 Go to previous message
Messages: 611
Registered: July 2006
Senior Member
I just posted an article on that site http://www.orafaq.com/node/926 ( removing duplicate indexes )

Previous Topic: how to view all created users of sql (oracle).
Next Topic: Slow response
Goto Forum:

Current Time: Tue Oct 25 20:18:48 CDT 2016

Total time taken to generate the page: 0.09465 seconds