Workload Characteristics and Row Partitioning
Consider using row partitioning when your workloads have any of the following characteristics:
This might be a good candidate for partitioning, but you need to pay particular attention to weighing the performance tradeoffs that often result in this situation.
For instance, instead of the following row partitioning expression.
PARTITION BY column
PARTITION BY RANGE_N(column BETWEEN m
Dates and timestamps are often used in query conditions and therefore makes good candidates for a partitioning expression.
When partitioning on a DATE column, use RANGE_N with a single overall range divided into ranges of equal size as follows.
PARTITION BY RANGE_N(date_column BETWEEN DATE '...'
AND DATE '...'
EACH INTERVAL 's' t)
TIMESTAMP '2011-08-25 10:14:59'to specify the ranges in a partitioning expression. This is not only easier to read, making it clear that the expression is defined on a date, but it also removes the dependence on the FORMAT used in the implicit conversion to a date. You can also specify TIMESTAMP(n) WITH TIME ZONE constants for RANGE_N partitioning.
For example, you can specify a RANGE_N‑based partitioning expression like the following.
RANGE_N(ts BETWEEN TIMESTAMP '2003-01-01 00:00:00+13:00'
AND TIMESTAMP '2009-12-31 23:59:59-12:59'
EACH INTERVAL '1' MONTH)
Use an INTERVAL constant in the EACH clause where the variable t is DAY, MONTH, YEAR, or YEAR TO MONTH.
Do not use INTEGER values or CHARACTER constants for dates in your partitioning expressions, since they can easily be incorrectly specified such that they do convert to the date you expected.
For example, it might seem intuitive to simply partition by the name of a DATE column as follows:
PARTITION BY sales_date
This form does not produce a syntax error. In fact, it works correctly only for dates in the early 1900s and follows the rule of implicit conversion to get an INTEGER partition number, but such a table is not generally useful.
For this case, you should instead use RANGE_N with a granularity of
EACH INTERVAL '1' DAY.
It might also seem intuitive to specify something like the following to indicate that a date column in the primary index is to be partitioned by week:
PARTITION BY 7
However, this form does produce a syntax error.
For this case, use RANGE_N with a granularity of
EACH INTERVAL '7' DAY.
By limiting ranges to those that are currently needed, you help the Optimizer to better cost plans and also allow for more efficient primary index access, joins, and aggregations when the partitioning column is not included in the primary index.
This is not as important if you collect current PARTITION statistics.
A good guideline is to define 10% or fewer of the partitions to be empty to be able to handle future dates. You should also define enough future ranges to minimize the frequency of ALTER TABLE statements needed to drop and add ranges. However, if you make changes too infrequently, you might forget to alter the table entirely.
Altering the table only once a year is probably not a good idea for the following reasons:
You must balance these concerns of having enough future partitions, but not too many.
Do not use an expression such as in place of a simple column reference in a partitioning expression constructed from a RANGE_N function even if the expression only references a single column.
Multiple ranges, with or without specifying an EACH granularity, require more CPU time to execute and different sized ranges can prevent fast partitioning changes.
Using these partitions can degrade query performance because queries can be forced to scan data in these partitions unnecessarily. If you specify the NO RANGE, NO RANGE OR UNKNOWN, or UNKNOWN partitions, you can also affect the performance of ALTER TABLE partitioning change negatively because data might need to be moved among the partitions. An UNKNOWN partition is not needed if the partitioning cannot produce rows with unknown partition values (this is often the case when a partitioning column is specified to be NOT NULL).
If the assumption made by the Optimizer that the table (not based on a CASE_N partitioning expression) has 65,535 partitions provides good plans in these cases, you do not need to alter partitioning, and the system produces the plans and data maintenance performance your site requires.