Databases Reference
In-Depth Information
ing search appeared to be based on a simple
z
-order search over the partitioning designs
ranked by individual benefit.
12.11
Range Partitioning Design
The Microsoft Database Tuning Advisor provides design automation for range parti-
tioning. Again, the implementation is based on what-if analysis using the query opti-
mizer. Predicates on range expressions provide clues for the boundaries of the horizontal
partitioning that the DTA will recommend. The candidate partitioning is detected by
the DTA detecting range and inequality predicates for table access as specified within
the incoming SQL text. For example, queries with predicates on date can imply a rea-
sonable strategy for range partitioning. For example, consider the following two predi-
cates that could easily appear within a database workload:
Ship_date < 01-01-1997
Ship_date < 02-01-1998
Clearly these expressions suggest that range partitioning by month may be reason-
able. The DTA parses inequality and range predicates from the workload to detect rea-
sonable range partitioning schemes, and evaluates these through the query optimizer
using what-if analysis. A novel aspect of DTA's range partitioning recommendations is
that is able to also incorporate manageability requirements in its analysis. For example,
a DBA can require that all tables and indexes must be “aligned”, i.e., partitioned iden-
tically. Such a constraint is useful since it makes managing the database (e.g., backup/
restore, loading new partitions etc.) easier. DTA searches the space subject to this
manageability constraint. More details are available in [Agrawal 2004]. The ability to
consider administrative within the automated design analysis is a very important
because, as discussed in Chapter 7, range partitioning is heavily used to improve data-
base administration.
Oracle's ILM Assistant recommends partitioning based on a lifecycle definition.
This is not workload based or cost based analysis.
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. Provide a complete workload as input to the advising utility.
The wiz-
ards/advisors for physical database design selection work best because they can
make tradeoffs about physical database design. While they are quite useful to help-
ing resolve problems with individual slow-running queries, they are really
designed best to make choices over sets of queries, inserts, updates, and deletes.
