Search code examples
sap-ase

Switch partition into existing table

Oracle and SQL Server have this feature which allows to switch a tables partition into an existing table (its discussed for example here). I am unable to find a similar feature for Sybase ASE.

Question Part A: Is there a way to switch partitions in Sybase ASE?

If the answer is 'no' I am unsure how to proceed. My situtation is that I have a very large table which is indexed by dates. Now I need to add data for a new Date T_n+1.

large table             new
--------------------   ------
|T1|T2|T3| .... |Tn| + |Tn+1|
--------------------   ------

The insert is fast enough if I drop the index on the table first, but the recreation of the index takes a lot of time. There has to be a better way!

Question Part B: what is the fastest way to add this data for Tn+1 into the large table.

Solution

  • Answer Part A:

    While Sybase ASE supports move partition and merge partition, these commands work within a single table, ie, Sybase ASE does not support the movement of partitions between (different) tables.

    Answer Part B:

    Assuming dropping and recreating indices is too expensive (in terms of time; in terms of users needing indices to access other partitions), you're not left with a lot of options to speed up the inserts other than some basics:

    • bulk insert
    • minimize the number of transactions (ie, reduce number of times you have to write to the log)
    • disable triggers for the session inserting the data [obviously you would need to decide how to handle any must-have logic that resides in the trigger]
    • bump up the lock escalation threshold (for the table) to insure you don't escalate to a table-level exclusive lock; only of interest if you can't afford to block other users with a table-level exclusive lock; may need to bump up the number of locks configuration setting; less of an issue with ASE 16.0+ since the insert should only escalate to a partition-level lock
    • if you are replicating out of this table you may want to consider the overhead of replicating the insert vs directly inserting the data into the replicate table(s) [would obviously require disabling replication of the inserts to the primary table]