Databases Reference
In-Depth Information
Figure 12.15
SQL Access Advisor Results page.
ever, the level of detail that is normally kept by default is often not enough to accurately
project storage requirements for materialized views and multidimensional clustering.
It's true that query optimizers model the I/O and selectivity of many complex situ-
ations that can occur within SQL, but those models are often compromised because
they were designed to be adequate for selecting optimal query execution plans and
therefore are approximate, only needing to be accurate enough to select stable high-
quality query execution plans for queries, not accurate enough to always predict runt-
ime requirements with precision. You can be sure that these models are always designed
to be as close to reality as possible, but the fact that the system continues to operate and
function if the model is slightly inaccurate (or even significantly inaccurate) limits the
need for perfection. However, when designing the physical structure of a database fea-
tures like indexes, materialized views and multidimensional clustering can require sig-
nificant additional storage, which translates into additional capital cost for the server. To
solve this vendors have introduced the notion of data sampling to the problem of auto-
mated physical database design. Sampling was discussed earlier in Chapter 10.
Many of the sampling ideas introduced there are replicated in automated form in
the utilities that recommend physical database design. Of these, the size of MDC and
materialized views are the most important to model with improved accuracy, because
the query optimizer is inadequate in these. Some products have advisors that automati-
cally sample data to better calculate index statistics, though in the opinion of the
