Chapter 8. Partitioning for Manageability
Back in Chapter 3, two critical points were made: Fact tables are so large that your success will largely hinge on their implementation, and fact tables should be partitioned to improve their manageability, not for quicker end-
I find that most DBAs eagerly partition their facts since it seems intuitively obvious that anything that large should be partitioned. However, they often partition their facts for the wrong reasons, and sometimes using the wrong, or even the worst, partitioning criteria. About half do so to improve query response times. The belief is that the best queries are those done in parallel against partitioned and sub-partitioned tables. But the fact is (as explained back in Chapter 5) that obtaining the star transformation explain plan is the most critical aspect for ad-hoc queries. In fact, it is so important that it does not really matter whether the table is partitioned or not. Yes, queries will run faster against partitioned fact tables, but 98% of the query speed will be from achieving the correct star transformation explain plan. Partitioning will simply be "icing on the cake" in terms of speeding up the end-users' ad-hoc queries.
Therefore, DBAs typically partition for the wrong reasons, and possibly sub-optimally as well. For example, consider the DBA who partitions a fact along a
Other DBAs partition their facts to improve data loading batch cycles. But remember, adding rows to a table really is not dependent on the size of that table. You can add a million rows to a billion-row table just about as fast as to an empty one,
One last issue that often muddies the water regarding fact table partitioning is this: DBAs may define their partitioning schema under Oracle 8i (with its