Home » SQL & PL/SQL » SQL & PL/SQL » Single Index with many columns or Multiple Indexes with fewer columns? (10g Release 10.2.0.3.0, Windows Server 2003)
Single Index with many columns or Multiple Indexes with fewer columns? [message #320801] Fri, 16 May 2008 04:57 Go to next message
smora
Messages: 59
Registered: May 2006
Member
Hi

Im deciding between a couple of index options:
Which will give a better performance boost, 1 index with all the columns that I query on, or multiple indexes, each with 1 of the columns I query on?

create unique index SG_IDX1 on XXX (col1, col2, col3, col4, col5) tablespace USERS;

OR

create unique index SG_IDX1 on XXX (col1, col2, col3) compress 2 tablespace USERS;
create unique index SG_IDX2 on XXX (col1, col2, col4) compress 2 tablespace USERS;
create unique index SG_IDX3 on XXX (col1, col2, col5) compress 2 tablespace USERS;

Thanks in advance
SM
Re: Single Index with many columns or Multiple Indexes with fewer columns? [message #320806 is a reply to message #320801] Fri, 16 May 2008 05:05 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have a read through this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1007132

Regards

Raj
Re: Single Index with many columns or Multiple Indexes with fewer columns? [message #320820 is a reply to message #320801] Fri, 16 May 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It all depends on your queries.

Database Performance Tuning Guide
Chapter 15 Using Indexes and Clusters
Section 15.1 Understanding Index Performance
Subsection 15.1.3 Choosing Columns and Expressions to Index

Regards
Michel
Re: Single Index with many columns or Multiple Indexes with fewer columns? [message #320831 is a reply to message #320801] Fri, 16 May 2008 06:57 Go to previous messageGo to next message
smora
Messages: 59
Registered: May 2006
Member
Thanks Rajaram, that was helpful!
Re: Single Index with many columns or Multiple Indexes with fewer columns? [message #320832 is a reply to message #320801] Fri, 16 May 2008 07:00 Go to previous message
smora
Messages: 59
Registered: May 2006
Member
Thank you Michel!
Previous Topic: IN OUT parameter
Next Topic: bulk export
Goto Forum:
  


Current Time: Wed Dec 07 14:23:34 CST 2016

Total time taken to generate the page: 0.09292 seconds