| 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
|
|
|
|
| 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.
|
|
|
|
|
|
| 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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
