(…) Are all those partitions critical, or only a relative few?
If that’s the case, you could: 1) detach the non-critical partitions 2) take the system down for maintenance 3) update the critical partitions 4) take the system up again 5) update the non-critical partitions 6) re-attach the non-critical partitions
That could shave a significant amount of time off your down-time. I would script the detach and re-attach processes first, to save some extra.
Thank you so much.
The partition table which we are planning to apply the ALTER script is a child table to another big partition table. And we have foreign key defined on table level but not partition to partition. So will detaching the partitions and then altering column of each detached partition and then re-attaching will revalidate the foreign key again? If that is the case then the re-attaching partition step might consume a lot of time. Is my understanding correct here?
Additionally , if we are okay with the 7.5hrs of down time , is my calculation/extrapolation of total time consumption based on a sample table, for direct alter, accurate? Because, in that case , I was thinking it's less complex and also less error prone to just do it in a single alter command rather than going for multiple steps of detach, alter, attach partition.
