What is the syntax to overlap partitions? Lets assume I want a year
split amount 4 months (3 partitions for a year) and later adding an
additional set of partitions for every 6 months. Plus giving us the
option to detach old partitions.
For example:
CREATE TABLE orders(id INT, shipdate DATE, …)
PARTITION BY RANGE(shipdate)
(
PARTITION m12y05 STARTING MINVALUE,
PARTITION m4y06 STARTING '1/1/2006',
PARTITION m8y06 STARTING '5/1/2006',
PARTITION m12y06 STARTING '9/1/2006'
ENDING ‘12/31/2006'
)
========================
-- Detach old data
detach partition m12y05
============================
-- Later applying additional partitions
Alter table orders
Add Partition
(
Partition s6y06 starting '1/1/2006',
Partition s12y06 starting '7/1/2006' ending '12/31/2006'
)