- Large tables and join indexes are typically better candidates for row partitioning than smaller tables and join indexes, because there is little benefit to partitioning a table or join index small enough that a full-table scan takes seconds.
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.
- When possible, row-partition on sets of columns that are frequently used as query conditions. For example, if half the queries against a table specify a date range that qualifies less than 25% of the rows, then that date column is a good candidate to be the partitioning column for the table.
If there is no column that is frequently used as a query condition, there is typically little or no advantage to row-partitioning the table.
- All other factors being equal, partitioning on a column set that is part of the primary index column set is better than partitioning on a column that is not.
The exception to this is if the primary index is rarely, if ever, used for row access or join operations.
- Keep the number of row partitions relatively small. The key word in this guideline is "relatively." The guideline also applies for multilevel partitioning situations, though more difficult to achieve for multilevel partitioning because the total number of partitions is a multiplicative factor of the number of partitioning levels defined for the table, so the number of partitions can grow quickly even when the number of partitions defined for each level is small.
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 for an example of evaluating these sorts of tradeoffs.
- Keep the number of partitions small even if you plan to expand predetermined table operations. You can increase the number of partitions later if needed.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 for multilevel partitioned tables. This guideline 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.
- The same criteria for selecting the column set for a nonpartitioned table also apply to partitioned tables.
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 can be conflicting considerations, so you must evaluate their relative merits and come to a compromise.
The following guidelines provide a high-level set of criteria for making an initial evaluation of whether row partitioning provides more benefits to a query workload than a nonpartitioned table.