I'm Working with a new Oracle DB, with one table having the following indexes:
- Index 1: ColA, ColB
- Index 2: ColA
Is the second index redundant, and Will this have a negative impact on performance?
I'm Working with a new Oracle DB, with one table having the following indexes:
Is the second index redundant, and Will this have a negative impact on performance?
The second index is sort of redundant - any operation that uses Index2 could use Index 1. Also, writes will be slightly slower since there is another index to update.
That said, Index2 is not entirely redundant as it could be a bit faster since the index itself is probably going to be significantly smaller.
there is a chance that if your statistics go out of date, the optimizer might choose index 2 when index 1 is needed. (a hint to the optimizer would solve that, of course.)
Google is my best friend :
http://www.orafaq.com/node/926
The main point of this article is :
If 2 indexes ( I1 and I2 ) exist for a table and
the number of columns in Index I1 is less or equal to the number of column in index I2 and
index I1 has the same columns in the same order as leading columns of index I2
Then
If index I1 is UNIQUE then
If index I2 is used to support Foregh Key or for Index Overload then
Do Nothing
Else
Index I2 can be DROPPED
End If
Else
Index I1 can be DROPPED
End If
End If
And I'm agree with that ! In fact, search "duplicate indexes" in Google to have different kind of answer.
The second index is different and is not redundant per se.
How about this query:
SELECT DISTINCT ColA FROM TABLE WHERE ColA IS NOT NULL;
Oracle can answer this question entirely from Index 2. Now, index 2 would be expected to be small (less blocks) than index 1. This means, it is a better index for the above query.
If your application never does a query that suits Index2 better than Index1, then it is redundant for your application.
Indexes are always a performance tradeoff. When an insert, update or delete is performed there is extra work to do in order to maintain each additional index.
Is this more than compensated for by the increased performance provided by the index? Depends on your application and data usage.