Raiders of the data dictionary — Indexing for the workload

Have you ever wondered about the quality of the index design in one your applications? After all, quite often the index design is done by developers who never see the database in action. How about not just fitting one index into existing complex indexes, but to create a complete new index design where everything fits together optimized as a whole. Would that not be very risky? How do you control the risk? How would you form your team? Where will you get the relevant information to do this? What tools would you use?

In the Raiders of the data dictionary series we use the information of the dictionary (e.g. the v$ views) to improve the physical database design.

Have you ever wondered about the quality of the index design in one your applications? After all, quite often the index design is done by developers who never see the database in action. How about not just fitting one index into existing complex indexes, but to create a complete new index design where everything fits together optimized as a whole. Would that not be very risky? How do you control the risk? How would you form your team? Where will you get the relevant information to do this? What tools would you use?

In the Raiders of the data dictionary series we use the information of the dictionary (e.g. the v$ views) to improve the physical database design.

I have used the exhaustive knowledge that the database holds about its own structure in many ways during my ten years with Oracle Consulting. Never before or again I used it so exhaustive as in the one assignment – a complete re-indexing of a large online database. In this experience report we will reflect on many aspects of such process, not only on the technical challenge to produce a good design that is optimized as a whole, but also on organizational and psychological aspects.

The article to this presentation was published in the May issue of the NoCOUG magazine.