Tuning 'Alter Modify" column
From: yudhi s <learnerdatabase99_at_gmail.com>
Date: Tue, 23 Aug 2022 15:05:05 +0530
Message-ID: <CAEzWdqc70Tu36bChvwmwT3ZKR58sUkr1eoe1_TyyFXaYhVamTg_at_mail.gmail.com>
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.
Date: Tue, 23 Aug 2022 15:05:05 +0530
Message-ID: <CAEzWdqc70Tu36bChvwmwT3ZKR58sUkr1eoe1_TyyFXaYhVamTg_at_mail.gmail.com>
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-lReceived on Tue Aug 23 2022 - 11:35:05 CEST