Databases Reference
In-Depth Information
4.
Materialized views.
5.
Indexes.
This ranking suggests that shared-nothing partitioning and MDC should be
designed first, and index design last with the goal of minimizing the inconvenience of
database redesign. In practice, few database designers design new databases in this
sequence. However, understanding the relative pain of changing design choices does
seriously impact the design planning process. Understanding the relative inconvenience
usually means that database designers are loathe to design shared-nothing partitioning
and MDC casually because the overhead of redesign is quite high. Decisions for shared-
nothing partitioning and MDC are usually made carefully, slowly, and applied with the
assumption that the design will not be tinkered with for a long time. In contrast, data-
base designers often try out new indexes and materialized views with relative freedom,
knowing that if the attempting index was not significantly worthwhile, it can be easily
dropped without much disruption.
9.3
Impact-first Waterfall Strategy
Another strategy in designing databases is to focus on the dominant design point first
and work toward refinement of the design by adding the features that are likely to have
less impact. This is extremely difficult to assess a priori because the impact of a design
choice depends dramatically on the definition of the workload and data in the database
(as well as many other factors such as system resources and topology). There are many
documented cases where any of the design choices discussed in this topic have improved
system performance by an order of magnitude or more.
However, most practitioners will agree that indexes are the first and most dominant
design requirement for databases because without them the query execution plans are
doomed to include table scans for many lookups and joins even in the presence of a rich
set of materialized views, for any sizable table will prove extremely inefficient. It is
almost impossible for real-world systems to avoid the need for indexes by other means
(such as purely through the use of materialized views) since indexes tend to be generic in
their utility, providing value to a wide range of queries that use the leading key of the
index, which other design features tend to be more specific-to-specific query fragments.
Following index design, the relative impact is less obvious. In environments where
materialized views are usable, they can reduce the cost of individual queries by orders of
magnitude, and are therefore the next most important design choice. However, as dis-
cussed above, indexes on the materialized views need to be considered during the design
of the materialized views and not as an afterthought.
Shared-nothing partitioning is usually the next most important design choice, usu-
ally impacting system performance in the range of 10 to 30%.
