I've discovered that the partitions used on a particular table are hurting query performance and would like to remove the partitions from the table. Is there an easy way to do this? The table in question has 64 partitions. Based on some initial investigation, I've come up with the following options. Is there a better way?

Copy data into another table, drop all partitions, then copy the data back into the original table

Copy data into another table, drop the original table, then rename the new table and rebuild the indexes

1 Answer
1

Personally I think a variant on option 2 sounds like the best approach.

Create new table as select * from
old table

Build indexes on new table (*)

Rename old table to something
different

Rename new table to old table name

Switch any foreign key constraints from
old table to new table. Also transfer any grants, synonyms, etc.

Drop old table in slow time

(*) A non-partitioned table will probably require different indexes from the partitioned one. If you are particularly attached to the index names you can use ALTER INDEX ... RENAME TO ... syntax after step 6.

The advantage of this approach is that it minimises your downtime (basically steps 3, 4 and 5).