Database Reference
In-Depth Information
performance issues (such as reading the EXPLAIN output to solve any opti-
mizer problems). Experience has shown that one 50/50 specialist per 5 to 10
application developers works quite well.
Index design requires technical skills and application knowledge. It is proba-
bly easier to teach indexing skills to application developers than to make database
specialists familiar with the details of the application.
COMPUTER-ASSISTED INDEX DESIGN
It is surprising that index design tools did not appear on the market until the
nineties. The first tools were rule-based, but many current tools use the optimizer
to evaluate alternative indexes. The objective of these tools is to find a set of
indexes that minimizes the average response time for a recorded workload (refer
to Fig. 16.1).
It is generally quite fast to find the best index for a given SELECT, but this
approach may lead to too many indexes. The tool must then find the least valuable
indexes. This is obviously a very time-consuming task if there are many different
SELECT statements. Think about how many cost estimates would be needed to
evaluate the effect of dropping a single index. As the tool developers say, it is a
huge search space
. Heuristic shortcuts are necessary to keep the execution time
within acceptable limits. These may indeed affect the quality of the proposal,
as Figure 16.2 suggests. Furthermore, the optimal index set is not necessarily a
subset of the ideal indexes.
Many current tools are like chess computers—it is possible to set limits for
the duration of the think time. The result is then the best proposal that the tool
is able to produce within the given time.
1
Ideal indexes
for each
SQL statement
Production
workload
SQL calls
Frequencies
OPTIMIZER
N
Y
Enough
space?
Statistics
Number of rows
Number of pages
Column cardinalities
Column value
histograms
etc.
END
2
Pruned index set
OPTIMIZER
Objective:
Minimize average LRT
Figure 16.1
Computer-assisted index design.
Search WWH ::
Custom Search