Re: Tuning 'Alter Modify" column

Because you're using a char() column (rather than varchar2() Oracle has to update every row that has a value (i.e. is not null) by appending a space to it, and it also has to update every index that references that column doing a "delete/insert".

The update will be by tablescan, in a single transaction, so you will acquire a HUGE undo segment. The redo generated will be one entry of about 280 bytes per row updated IF THERE ARE NO INDEXES; plus you might find that some rows migrate but that's a little unlikely unless the rows are all very short to start with anyway.

For every index that includes the column you're likely to generate a bigger problem. Every "updated" index entry will be an insert/delete pair, generating at least 450 bytes between them (plus 2 x the length of a typical index entry). Moreover, because it's a single transaction and Oracle doesn't cleanout, or even try to "stub" index entries until the transaction has committed (and even then some other session has to do the cleanout) you will basically double the size of the index and probably generate more redo on index leaf block splits - which generate about 18KB of redo per block split if you're using 8KB blocks.

A further time-waster is that Oracle has a funny "restart" mechanism the first time the undo segment its using needs to add an extent (see: https://jonathanlewis.wordpress.com/2019/09/10/update-restarts/ and its comments). At the scale you're operating your modify is likely to work for a few million rows than roll back and start again.

Given the huge amount of redo and the catastrophic impact on the indexes options are:
a) try dropping any affected indexes before you do the modify - this MAY help.
or
b) use dbms_redefinition to make the change if you need it to be online or
c) Create an empty single-partition partitioned table matching the definition. Insert /*+ append */ from old to new. Create local indexes matching your current indexes on the partitioned table. Exchange partition with table. This means you only copy the table once (and minimise the redo) and don't lose privileges etc.- but there are some little details you would want to investigate and adjust around the edges of this strategy.

Regards
Jonathan Lewis

On Tue, 23 Aug 2022 at 10:35, yudhi s <learnerdatabase99_at_gmail.com> wrote:

> Hi All, We have got an ALTER table modify column column in which we are
> increasing the length of a column from char(8 byte) to char(9 bytes) as
> below. The table holds ~100million rows. We are seeing in the long ops its
> showing to take 4-5hrs of time and the message showing up as 'Table Scan'.
> I think one way is to move the data to another table and then truncate this
> table and modify the column and then move the table back using INSERT
> APPEND.But we were trying to understand if there exists any other less
> impact and faster approach like making it happen in parallel threads etc. I
> tried making the table Degree to parallel-16, buty the ALTER is still
> running in serial.
>
> alter table tab1 modify COL1 CHAR(9 BYTE);
>
> Regards
> Yudhi
>

--
http://www.freelists.org/webpage/oracle-l