Re: Tuning 'Alter Modify" column

Okay. Actually I ran it as below. And I did test it with parallel and noparallel on dev on a ~2million row table and both are taki9ng same amount of time. So it means the parallel one is not really helping.

alter session force parallel ddl;
alter table tab1 parallel 16;
alter table tab1 modify COL1 CHAR(9 BYTE);

On Tue, Aug 23, 2022 at 3:24 PM Pap <oracle.developer35_at_gmail.com> wrote:

> How did you check if the ALTER query ran in parallel or not post making
> the table degree parallel?
> Because sometimes you may see that ALTER itself is executing in serial but
> the recursive sqls that do the update the column value from char(8) to
> char(9) are running in parallel.
>
> On Tue, Aug 23, 2022 at 3:05 PM 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