Organizing the Index Design Process - Relational Database Index Design and the Optimizers - page 287

Chapter 16

Organizing the Index Design
Process

ž How the index design process may be organized with regard to the
responsibilities of the people involved
ž Use of computer-assisted index design tools
ž Their objectives and their strengths together with their shortcomings
ž Summary of the basic estimation process
ž Nine steps toward excellent indexes

INTRODUCTION

At the beginning of the database age, index design was treated as a centralized
function within the specialist group. The applications used to be so simple that
a single database specialist was able to become familiar with all the processing
requirements. Today this is seldom possible.
These days, each application programmer would evaluate the current indexes
when writing a new SQL statement, at least with the basic question; ideally they
should also use the QUBE to check the performance with the worst input. If
the performance appears to be unsatisfactory, they could consider any required
amendments. If necessary, the SELECT statement could be referred to a database
specialist, possibly with the proposed additional columns to an existing index,
or perhaps even with a suggestion for a new index. The final decision could
be centralized, at least with integrated applications at the corporate level; the
specialists are in the best position to estimate and control the overall costs and
side effects.
Delegating the responsibility for reacting to index alarms certainly leads to
the best results. Many organizations feel, however, that the required education
and mentoring (about 1 to 2 weeks) is too large an investment.
A reasonable compromise in large organizations would seem to be to nomi-
nate 50/50 specialists; they would spend 50% of their time in application devel-
opment and 50% in assisting their colleagues in index evaluation and other