Re: Index strategy

> I now work at a shop where the developers over the years have added
> many (random) indexes on the database, and (still) having problems
> with performance. I see many sql-statements coming by that use
> suboptimal indexes, I think it would be a good idea to mass-review the
> sql and indexes, increasing the quality of the indexes and reduce the
> 'overindexing' of the DB. Seniors! I assume you have encountered this
> situation before. How to efficiently deal with such situation?

Well it is a divide and conquer strategy type of plan that probably will work best. Not quite sure what you mean by "mass-review".

I would probably start by looking at the top 5 or 10 tables and/or indexes ( by gets and/or by reads ) and seeing what falls out of the trees. If you are licensed for AWR stuff the reporting makes it pretty obvious where you will get the best bang for your buck.

Finding some examples and case studies where you document some improvements ... how and why ... what gain it gives to your business by fixing the design ... is one way to go.

It can get exhausting mentally if you are in this battle by yourself. It can get to be fun if you can get some buyin from the developers and as their knowledge base increases if they start to suggest changes themselves.

Ultimately there is no one size way of efficiently reviewing everything nor is it going to be cost effective. Received on Wed Jun 02 2010 - 08:13:48 CDT