Sybase ASE : Table partitioning

Question

I am working on partitioning a huge table in Sybase. The use case is, the table has grown over the years and the applications do not need data beyond let's say 6 months. So, I need to archive some of the rows. I am exploring the following approach:

• Add date partitioning into table and partition the rows by day
• Write a batch that executes every midnight and archives the partition that is more than 6 months old

Now, in order for me to do this, I need to partition the existing table and have a couple of questions (I am not a Sybase or a DB expert):

• Range partitioning in Sybase comes closest to the approach I am trying to implement. However, it requires you to specify all the ranges upfront. So, if I want to create daily partition on rolling basis for a year, do I need to specify all the values up front?
• Can I alter an existing table and add partitions or I need to create a new table?

Answer 1

Partitioning is meant to:

1. Reduce time in maintenance tasks (dbcc, update statistics, and so on)
2. Savings in disk usage (i.e. use slow disks for cold data)
3. Achieve performance gains (with important previous considerations)

If you are in cases 1 or 2 the answer is:

To create a range partition you don't need to set an upper value; the upper value for the range is implicitly defined when you create the next partition. The next partition can be defined at any time by means of alter table

If you are in case 3:

Be very careful defining the partition columns; ideally they must match the where clause of your queries. Use a meaningful global index, so that ASE is able to find the location of the rows in every partition. Use local indexes for the rest.