Databases Reference
In-Depth Information
11
Query Execution Plans
and Physical Design
It is a bad plan that admits of no modification.
—Publilius Syrus (~100
B
.
C
.), Maxims
Make a new plan Stan … and get yourself free.
—Paul Simon
hroughout this topic we've presented the major attributes of physical database
design, and how they are used and why. How can you determine if a specific data-
base design attribute is really helping or hurting your database? Or even more funda-
mentally, how can you determine if a design feature is being used by the database?
There's a brute-force method of course: Run experiments with your application work-
load to evaluate the contributions of every single physical design change to determine
the impact. This kind of brute-force strategy is probably inefficient since it means actu-
ally creating and testing each design possibility with a real database packed with a lot of
data. There's another way to evaluate the benefit of design choices, and interestingly it's
the same method that the database itself uses; namely, to examine the impact of design
choices on the query execution plans of your workload. This technique is in fact the
dominant technique used by database administrators (DBAs) and database designers for
every major database product, and as a result every major database product provides
tooling to allow users to examine the query execution plans chosen by the database for
each query. By viewing the query execution plan one can observe whether the database
itself thinks that design choices, such as materialized views and indexes, are likely to be
T
197
