Analysis of Partitioning Benefits
The primary index access queries that were run against the nonpartitioned version of this table must be reformulated to use the USI to access the row. As a general rule, accessing a row takes roughly two to three times longer using a USI than it would using a UPI. However, UPI access is a very fast operation, so doubling or tripling the time might barely be noticeable to the users who issue those queries.
Without row partition elimination, direct Merge Joins require, at best, more memory and CPU utilization and might be measurably slower compared to a similar nonpartitioned table. The extent of performance degradation depends on the query conditions, how many partitions can be excluded, and the specific join plan chosen by the Optimizer. Actual measurement of representative queries is necessary to determine the overall difference in performance.
The nightly inserts benefit in the same way, and for the same reasons, as in “Scenario 1” on page 427. However, the additional index on invoice number partially offsets that benefit. The same considerations apply to the monthly delete operations.
The ad hoc queries examining several months of invoices benefit in the same way as in “Scenario 1” on page 427. The benefit is greatest when fewer months are examined.
Would it be worthwhile to convert the invoice table to a partitioned table? The DBA must measure the degree of improvement as well as the extent of degradation in the various types of query, and use that analysis to determine how much each query type contributes to the overall workload involving this table. This exercise produces a good estimate of the comparative workload performance against the table with and without partitioning.
If the measured performance difference between the otherwise equivalent partitioned and nonpartitioned tables is substantial, in either direction, then the choice might appear to be obvious. However, you must also weigh the relative importance to the enterprise of the various activities in the workload.
For example, consider the following contingencies: