When the volumes are high, splitting a
single partition by hash or list is very useful also with respect to better disks utilization
(Figure 2).
Here, we have a range (logical) partition that is striped over n table spaces, each of
them containing one subpartition of every range partition; this organization (when
table spaces are allocated on different volumes) gives good performance and easy
manageability. Performance improves due to subpartitions (better I/O parallelization).
Manageability is simpler because we have a 1:n relation between table space
and partitions, so space allocation constraints are relaxed.1
As Figure 2 shows, indexes are partitioned in the same way as the table. Partitioned
or local indexes (an index that is in relation 1:1 with its partition) are the normality
in DW because they can be dropped/added as table partition, thus avoiding index
rebuilding. Different kinds of indexes (B*Tree and bitmap) can be defined as local,
but not all types are well suited for DW; in particular, bitmap indexes are only
useful with low cardinality and read-only access.
Extraction, Transformation, and Loading Processes
Copyright ?© 2007, Idea Group Inc. Copying or distributing in print or electronic forms without written permission
of Idea Group Inc. is prohibited.
Figure 2. Subpartitioning schema
When we have to build indexes, should it be done after load or during load? In the
case of high volumes, it is always preferable to build indexes after load (better performance);
for small/medium volumes, loading with active indexes is the simplest
way.
Pages:
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200