July 17, 2015

You might wonder why you should move a partition from one table to another but actually there are several reasons. The following one came up in a session I had with a customer who wants to archive “old” data. The actual data is partitioned (INTERVAL) and initially his idea was to move the rows to the archive table with INSERT AS SELECT. The archive table has to stay for a minimum of 10 years but is very seldom used. As there are global indexes on the current table with about 300 million rows we couldn’t simply keep the old data