Databases Reference
In-Depth Information
141
server). Within each range partition data is clustered using multidimensional clustering
(MDC) according to a regional geography. In the large this hierarchy is the most reason-
able one for the various ways of slicing and dicing data, with shared-nothing partition-
ing being the coarsest level of partitioning and MDC being used as the finest. A detailed
discussion of what MDC is and its benefits follows in Chapter 8.
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. When to use range partitioning:
a. Large tables, to ease the addressability concerns, will still use shorter RIDs. Some
vendors offer larger addressing using large (wider) RID formats, but this consumes
significantly more space in the indexes, where RIDs are stored.
b. Roll-in/roll-out. If you need to add or drop ranges of data, especially by date, you
have a classic need for range partitioning.
c.
Business intelligence-style queries, where partition elimination can offer significant
improvement in query execution plan selection.
•
Tip 2. Which column(s) to partition on:
The practical observation is that range par-
titioning is used predominantly for date columns. Roll-in and roll-out scenarios are
almost always based on dates. The second major use of range partitioning is for
improved query execution plan selection exploiting partition elimination, and a signifi-
cant set of those opportunities are also based on date predicates. However, look for
range queries not based on date predicates to find other column candidates.
•
Tip 3. Selecting the granularity of ranges:
When you are using range partitioning for
roll-in/roll-out, the most important design point is that the range partitioning match
the roll-in and roll-out ranges. For example, if you need to roll-in and roll-out data by
month, then range partitioning by month is a reasonable strategy.
•
Tip 4. Use range partitioning to improve the granularity of administration tasks
like backup and data load.
•
Tip 5. Use range partitioning to enable rapid deletion of ranges of data (roll-out).
This is particularly useful when your database is composed of a sliding window of data,
such as a weekly, monthly, or quarterly updated database, where you want to keep a
fixed history of data in the database. A typical example is a five-year data mart that is
updated monthly. Every month a new month's worth of data is added, and the oldest
month is rolled-out.
