Long running partition reorganization interferes with other operations

ftobin

Hi, what is the best way to perform a multi-day partioning so that it doesn't interfere with other tasks like deletes?

I have a long running partition reorganization that is taking multiple days. Some other steps in my workflow involve deletes, which fail as a result, since deletes need an exclusive lock. This is creating multiple headaches, especially since there doesn't seem to be a way to cancel, pause, or resume the reorganization.

Ideas are greatly appreciated!

Jim_Knicely

Maybe use the COPY_TABLE command to make a copy of the table. Then run the partition reorg on that table. Once that's done, you'd have to sync up any data changes (i.e. DML) from the original table to the new table, then drop the original table and rename the copied table to back to the original name.

Also, consider Partition Grouping and Hierarchical Partitioning to limit the number ROS containers.

baron42bba

If you delete lots of data try to partition the table in a way that you can drop partition instead.
If it is related to delete/ re-insert like a day of data use a staging table with the same partition and projection layout.
Then you can use swap_partitions between both tables and truncate the staging table instead.

ftobin

@baron42bba said:
If you delete lots of data try to partition the table in a way that you can drop partition instead.
If it is related to delete/ re-insert like a day of data use a staging table with the same partition and projection layout.
Then you can use swap_partitions between both tables and truncate the staging table instead.

Unfortunately it was neither of those cases. There was simply a process which needed to delete an uncompleted intraday load of a day's data and re-insert fresh to ensure integrity. Maybe not the best workflow, but what we have to live with right now.

But thank you for mentioning swap_partitions!