I have inherited a database that contains a large table (relative to the rest of the database - 10 million plus rows) of auditing data that needs to be retained for 60 days. Anything older than 60 days can be deleted.
I'm looking to partition this table by range (preferably by day), then run an event to keep things tidy by dropping partitions outside of the retention period.
The data in my table goes back around 6 months, so the first thing I'm going to do is run a manual delete on anything older than 60 days so I''m starting with a clean slate.
Then I need to create the partitions, but do I really have to list all my partitions in the alter table statement? i.e.
ALTER TABLE AuditData PARTITION BY RANGE( TO_DAYS(date) ) ( PARTITION part1 VALUES LESS THAN( TO_DAYS('2018-01-01 00:00:00') ), PARTITION part2 VALUES LESS THAN( TO_DAYS('2018-02-01 00:00:00') ), PARTITION part3 VALUES LESS THAN( TO_DAYS('2018-03-01 00:00:00') ), PARTITION part4 VALUES LESS THAN( TO_DAYS('2018-04-01 00:00:00') ), PARTITION part5 VALUES LESS THAN( TO_DAYS('2018-05-01 00:00:00') ),
... and so on
Is there a way to create the partition just by saying, partition the existing data by day?
I understand I will need to run a daily job to create a partition.