The main type of partitioning used in DW design is range partition; a table is usually
partitioned on a date column (partition key) and each partition contains rows
that fall in a range expressed by a start_date and end_date. As mentioned previously,
one can partition by ???date-of-fact??? or by ???loading-date???: partitioning by
date-of-fact is well suited for analysis, in that the optimizer can make an effective
partition pruning when the queries (as usual) have a ???where clause??? on the dateof-
fact; partition by ???loading-date??? is best suited for the ETL application, but can
limit the benefit of partition pruning. In any case, a table partitioned by date is
optimal for history management. At every loading cycle, (when necessary) a new
partition is added and the oldest one is dropped with a SQL operation that is very
fast and guarantees transaction consistency (the drop operation waits for in-course
selection on partition).
The hash partitioning means that data are split over a specified number of partitions
on the base of the value of a column and is useful for performance reasons (striped
data across multiple files/disks/devices allow parallelization and then improve performance).
Composite partition range-hash means that the single partitions will be
further split on hash base (not controlled by application) or on list base (controlled
by application) in range-list partitioning.
Pages:
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199