Databases Reference
In-Depth Information
This analysis leads one to conclude that the selection can have the following order:
1.
Materialized views and index design.
2.
Shared-nothing partitioning design.
3.
MDC design.
This analysis does not include range partitioning, an important design attribute dis-
cussed in Chapter 7. Extending the work of Zilio et al. to include range partitioning
might give a dependency table, such as Table 9.2.
Judging between range partitioning and MDC the generally accepted practice is
that range partitioning is a coarser higher-level granularity of partitioning. This suggests
the following order for design exploration:
1.
Materialized views and indexes need to be designed together and probably should
be designed first.
2.
Shared-nothing partitioning design.
3.
Range partitioning design.
4.
MDC design.
9.2
Pain-first Waterfall Strategy
In practice the consequences of changing the physical database design for some
attributes are much more challenging than others. For example, creating a new second-
ary B+tree index is a moderately costly effort, requiring (typically) a scan through the
entire table to extract keys, a full sort of these keys, and input/output (I/O) for the pages
for the index, which will include I/O for all the keys plus additional I/O for the inter-
mediate nodes of the tree. However, conversion of a table to MDC is dramatically more
expensive since it requires full reconstruction of the base table (with either sorting or
hashing of every record), reconstruction of all secondary indexes, and the creation of
additional new block indexes. It is not unlikely that conversion of a table to MDC will
require an order of magnitude more time and storage than the creation of a new second-
ary index. Similar comparisons can be made for shared-nothing partitioning, range par-
titioning, and materialized views. In general the hierarchy of “pain” looks something
like the following from most to least painful to redesign:
1.
Shared-nothing partitioning.
2.
MDC.
3.
Range partitioning.
