The following guidelines provide a high‑level set of criteria for making an initial evaluation of whether row partitioning would provide more benefits to a query workload than a nonpartitioned table.
The exception to this is a small table that is row‑partitioned identically to a larger table with which it is frequently joined and with which it shares its primary index.
If there is no column that is frequently used as a query condition, then there is probably little or no advantage to row‑partitioning the table.
The exception to this is if the primary index is rarely, if ever, used for row access or join operations.
The exception to this guideline is if the primary index is rarely, if ever, used for row access or direct merge joins.
For example, if all the queries against the table access at least one month of activity, there is little or no benefit to partitioning by week or day instead of by month. An exception to this is if bulk data loading times are greatly reduced by a finer partition granularity.
See “Scenario 4” on page 434 for an example of evaluating these sorts of tradeoffs.
Note: If you collect and maintain fresh statistics on the PARTITION columns of tables, this consideration is much less important.
You have greater flexibility with this guideline for single‑level partitioned tables than you do for multilevel partitioned tables because it can be rather complicated to decrease the number of partitions for a multilevel partitioning because the number of combined partitions defined for such a table increases multiplicatively with each partition and with each level defined.
Choose a primary index column set that provides good row distribution, avoids skew, and is commonly used to access individual rows or do not use a primary index.
Optimal row distribution and frequent access are sometimes conflicting considerations, so you must evaluate their relative merits and come to some compromise if that is the case.