Databases Reference
In-Depth Information
As part of the physical design, the global schema can sometimes be refined in limited
ways to reflect processing (query and transaction) requirements if there are obvious large
gains to be made in efficiency. This is called
denormalization
. It consists of selecting dom-
inant processes on the basis of high frequency, high volume, or explicit priority; defining
simple extensions to tables that will improve query performance; evaluating total cost for
query, update, and storage; and considering the side effects, such as possible loss of integ-
rity. Details are given in Chapter 15.
1.4
Why Physical Design Is Hard
Physical database design involves dozens and often hundreds of variables, which are dif-
ficult to keep track of, especially when their effects are very often interrelated to the var-
ious design solutions proposed. The individual computations of performance based on
a given index mechanism or partition algorithm may take several hours by hand, and
performance analysis is often based on the comparison of many different configurations
and load conditions, thus requiring thousands of computations. This has given rise to
automated tools such as IBM's DB2 Design Advisor, Oracle's SQL Access Advisor, Ora-
cle's SQL Tuning Advisor, and Microsoft's Database Tuning Advisor (DTA), formerly
known as the Index Tuning Wizard. These tools make database tuning and performance
analysis manageable, allowing the analyst to focus on solutions and tradeoffs while tak-
ing care of the myriad of computations that are needed. We will look at both manual
analysis and automatic design tools for physical database design in this topic.
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. The truth is out there, but you may not need it.
Every database has a theoreti-
caly perfect, or "optimal", physical design. In reality almost nobody ever finds it
because the search complexity is too high and the validation process too cumbersome.
Database design is really hard problem. However, the complexity is mitigated by the
practical fact that at the end of the day what matters most is not whether the database
performance is as good as it can theoretically be, but whether the applications that use
the database perform "good enough" so that their users are satisfied. Good enough is a
vague and subjective definition of course. In most cases, while the perfect database
design is usually elusive, one that performs more than 85% of optimal can be achieved
by mere mortals.
