Re: Column type modification in big tables

On Fri, Aug 16, 2024 at 2:04 AM Lok P <loknath(dot)73(at)gmail(dot)com> wrote:

>
> On Thu, Aug 15, 2024 at 9:18 PM Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>
>>
>> > On 15 Aug 2024, at 14:15, Lok P <loknath(dot)73(at)gmail(dot)com> wrote:
>>
>> (…)
>> 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.