Home » RDBMS Server » Performance Tuning » composite index or single column index?
composite index or single column index? [message #126793] Wed, 06 July 2005 12:16 Go to next message
liux99
Messages: 29
Registered: June 2005
Junior Member
I have table t1 and t2 and I have

query1
select * from t1 and t2
where t1.col1 = t2.col1 and
t1.col2 = t2.col2 and
t1.col3 = t2.col3;

query2
select * from t1 and t2
where t1.col1 = t2.col1;


I have composite index idx1 for t1(col1, col2, col3);

Will query1 take idx1? What about query2? do i need create another index on t1(col1)?

Thanks



Re: composite index or single column index? [message #126795 is a reply to message #126793] Wed, 06 July 2005 12:34 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
What happens if you test it and find out using explain plan or autotrace? You should be able to verify this very easily on your system.

But, in general, a query can use an index if the leading column of the index is used in the criteria. So having an index on t1(a,b,c) and another index on t1(a) would be redundent, and the t1(a) index should be dropped.
Re: composite index or single column index? [message #126801 is a reply to message #126795] Wed, 06 July 2005 13:04 Go to previous messageGo to next message
liux99
Messages: 29
Registered: June 2005
Junior Member
Thanks. What about t1(b) and t1(c), are they also redundant since I have t1(a, b, c)?
Re: composite index or single column index? [message #126803 is a reply to message #126793] Wed, 06 July 2005 13:15 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
In general, and with the exception of skip scans, no, they are not redeundant. Since those columns are not the leading column of t1(a,b,c), which would be a.

Suggest you read a bit in the concepts guide.
Previous Topic: SQL*Net messages
Next Topic: shutting down database
Goto Forum:
  


Current Time: Tue Jan 19 22:46:46 CST 2021