Vantage supports the BEGIN and END bound functions on Period columns in partitioning expressions anywhere that a DateTime expression is valid.
For information about how to optimize partitioning expressions based on updatable dates and updatable timestamps for reconciliation using ALTER TABLE TO CURRENT statements, see Partitioning Expression Based on Updatable Current Date and Timestamp Expressions.
- Use RANGE_N with a DATE partitioning column. For example,
PARTITION BY RANGE_N(date_column BETWEEN DATE '...' AND DATE '...' EACH INTERVAL 's' t)
- Use INTERVAL constants in the EACH clause, where t is DAY, MONTH, YEAR, or YEAR TO MONTH
- Use DATE constants for the ranges, for example DATE '2003-08-06'.
Do not use INTEGER or CHARACTER constants for dates.
DATE constants are a better choice for the following reasons.- They are easier to read, and it is clear that the ranges are over dates.
- They provide less dependence on the FORMAT.
- It may seem intuitive to partition by a DATE column such as PARTITION BY salesdate.
This does not return a syntax error; in fact, it works correctly for a few very old dates and follows the rule of implicit conversion to get an INTEGER partition number.
The problem is that a table partitioned this way is not useful.
Instead, use RANGE_N and EACH INTERVAL '1' DAY.
- It might seem intuitive to specify something like PARTITION BY 7 to indicate that a date column in the primary index is to be partitioned by week, but this syntax returns a syntax error.
Instead, use RANGE_N and EACH INTERVAL '7' DAY
- Consider having only as many date ranges as you currently need plus a few extra for future dates. In doing so, be certain to balance the following concerns.
- Limiting ranges to only those that are currently needed helps the Optimizer to cost plans better. It also allows for more efficient primary index access and joins if the partitioning column is not included in the primary index definition.
- Define future ranges in such a way as to minimize the frequency of adding and dropping partitions using the ALTER TABLE statement (see ALTER TABLE (Basic Table Parameters)).
However, if you perform your partition adding and dropping task too infrequently, you might forget to do them when they are needed to provide accurate query support.