Setting up semantic partitions ASE
Running ASE 15.5
We have a non-partitioned table with 640 million rows. We are looking at setting up range semantic partitions by create date
There are 200 + million rows for 2013 and 380 + million rows for 2014
I am thinking about setting up following partitions by create date:
Partitions 1 - 4 : For 2015 By quarter
Partition 5 - For year 2014
Partition 6 - 2013 and and earlier
Add new Partitons for each new year ...
Only updating current data -- i.e. any data more than month old is no longer updated ..
Is this a viable breakdown ?
1st attempt at partitioning ..
Mark A Parsons replied
Sounds ok to me, especially if you plan on using local indexes => should definitely reduce your maintenance window (eg, update stats, reorgs) if you only have to worry about the most recent couple months of data/indexes.
For each new year I'm assuming you'll be adding new partitions on a quarterly (3-mos) basis, too?
Do you plan on doing any sort of delete/purge processing on the 'old' rows at some point, and if so under what criteria? If you can partition based on your delete/purge criteria you'll likely find 'alter table/drop partition' to be a bit faster than running DELETEs (NOTE: global indexes will still need to be rebuilt).