OraFAQ Forum: Performance Tuning » composite index or single column index?

Home » RDBMS Server » Performance Tuning » composite index or single column index?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

composite index or single column index? [message #126793]

Wed, 06 July 2005 12:16

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

Report message to a moderator

Re: composite index or single column index? [message #126795 is a reply to message #126793]

Wed, 06 July 2005 12:34

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.

Report message to a moderator

Re: composite index or single column index? [message #126801 is a reply to message #126795]

Wed, 06 July 2005 13:04

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)?

Report message to a moderator

Re: composite index or single column index? [message #126803 is a reply to message #126793]

Wed, 06 July 2005 13:15

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.

Report message to a moderator

Previous Topic:	SQL*Net messages
Next Topic:	shutting down database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 08:33:07 CDT 2025