The ???SQL delete??? operation, when involving many thousands or millions of rows, is
very expensive in terms of time and disk space (rows must be saved in before-image
space and destroyed only after commit) but also involve heavy indexes reorganization.
Suppose that a fact table has 1 billion rows and a 10-day history; deleting
the rows related to 1 day (100 million) is just heavy, but this is nothing compared
to the job of rearranging five to six indexes that own 1 billion rows. Without partitions
(or without some surrogate techniques), it is practically impossible to manage
4 Adzic, Fiore, & Sisto
Copyright ?© 2007, Idea Group Inc. Copying or distributing in print or electronic forms without written permission of
Idea Group Inc. is prohibited.
large and very large fact tables, especially due to the index reorganization costs.
Enlarging history involves increasing the size of global indexes and so increasing,
in a nonlinear way, the cost of its access/reorganization. The local indexes resolve
this problem maintaining constant index reorganization costs at the level of a single
partition cost (accessing the entire table via a local index has indeed a linear cost).
In this way, it is possible to enlarge the history at the cost of increased disk storage
requirement. In the following section, we will see the different types of partitioning
and how they are suited for history management and for performance.
Pages:
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198