Databases Reference
In-Depth Information
combination of potential design attributes, the execution time for the what-if
analysis would be prohibitive. Reducing the analysis time requires the careful
design of a search algorithm.
5.
Most physical design attributes require additional storage. Indexes, materialized
views, clustering, and multidimensional clustering are all examples. As a result,
an infinite allowance for additional design attributes is unreasonable. Most
automated physical database design utilities solve this by defining an arbitrary
limit on the additional storage that can be used for new design attributes, and
by allowing the user to specify a storage limit if they choose.
6.
Query compilers are designed to model the processing time of queries for vari-
ous query execution plans given an existing physical design. They are not well
designed to model the increased storage requirements for specific physical
design attributes being potentially added to a system. The problem is not overly
hard, but requires logic outside of the query compiler. The compiler is a power-
ful tool for what-if analysis, but it is not sufficient.
12.2
Automated Design Features from Oracle,
DB2, and SQL Server
What's most notable about the technology offered by all three vendors is what they have
in common. All three vendors use the query optimizer to help select physical design
choices. From a set of candidates chosen in part by the query optimizer the advisors
then select the best candidates that provide the most benefit to the entire workload with
the least creation, maintenance, and storage costs. The advantage of integrating directly
with the optimizer compared to building a rule-based candidate generator is that the
recommendations are always guaranteed to be in sync with the query optimizer and
therefore have an extremely high probability of being used at runtime.
One of the most dangerous aspects of these features is the high dependency on a
reliable and complete definition of a workload. These advisors do a superb job at mak-
ing recommendations for a single query or a well-defined set of queries. However, from
a software engineering perspective, the objective is really not just to improve the perfor-
mance for the specified workload, but to also define a durable database design that gives
reasonable performance for a changing workload. It is not always helpful to overopti-
mize a database. The advisors attempt to deal with this by generalizing statements and
recommendations where possible, but much like a computer playing chess, they are cur-
rently limited in their thinking to what they see before them. Table 12.1 shows a high-
level functional list of the capabilities in three of the major automated design utilities
today for DB2, SQL Server, and Oracle.
Worth noting is that no single product has a superset of capabilities. This table is
just a checklist and what the table does not illustrate is the quality of the recommen-
