On 10/20/2010 05:43 PM, DM wrote:
> Composite Index question:
>
> I have composite index on 3 columns on a table, by mistake the composite
> index was created twice on the table.
>
> Will there any performance issues on this table because of the 2 same
> composite indexes?
>
> Thanks
> Deepak
Are the indices of the same type (e.g. both BTree) but with different index
names?
Is the second composite index the columns in same order as the first ? if
not in the same column order you might be seeing some benefit for some
queries but this is dependent on the queries filter clauses. If so you
might consider augmenting one or both of the indices to better suit your
queries.
From my experience, it appears to degrade performance because two indices
have to be maintained. (not to mention also appears to be a waste of disk
space) I am hopeful someone will correct me if I am wrong.
Ours were from people explicitly creating indexes on columns that had
indices implicitly created on them when the table was created. Cleanup was
pretty easy and painless on our production systems.
It's pretty easy to check for exact duplicates all over a given database as
well as how often each index is being used.
Check some of the queries here:
http://www.xzilla.net/blog/2008/Jul/Index-pruning-techniques.html
..:Mark