Column drop issue
From: Pap <oracle.developer35_at_gmail.com>
Date: Fri, 8 Apr 2022 09:49:20 +0530
Message-ID: <CAEjw_fgtLvwRJVvLaFpZpLsurR+7vOA5EVxX1NodYDmzdr66XQ_at_mail.gmail.com>
Hello Experts, This is a 19c database. We encountered a scenario in which an alter query got stuck and eventually failed after many hours with Ora-1555. It's a range partitioned table with ~600million rows and ~600GB in size and the team is trying to drop ~20 columns out of it using "Alter table drop(col1,col2,col3.. coln);" syntax. The wait event we are seeing is 'log buffer space' also 'cell multiblock physical read". At one point we checked, the "long ops" was showing a 'rowid range scan' and the estimated time it was showing was ~60hrs+. So wondering how to make this Alter succeed?
Date: Fri, 8 Apr 2022 09:49:20 +0530
Message-ID: <CAEjw_fgtLvwRJVvLaFpZpLsurR+7vOA5EVxX1NodYDmzdr66XQ_at_mail.gmail.com>
Hello Experts, This is a 19c database. We encountered a scenario in which an alter query got stuck and eventually failed after many hours with Ora-1555. It's a range partitioned table with ~600million rows and ~600GB in size and the team is trying to drop ~20 columns out of it using "Alter table drop(col1,col2,col3.. coln);" syntax. The wait event we are seeing is 'log buffer space' also 'cell multiblock physical read". At one point we checked, the "long ops" was showing a 'rowid range scan' and the estimated time it was showing was ~60hrs+. So wondering how to make this Alter succeed?
Had a talk with respective team, we got to know this table is holding ~256+ rows and encountering row chaining effect in queries , and as this table is having ~20+ columns which holds mostly nulls , so we want to have those columns placed towards the last of the row, so that row chaining effect can be eliminated. For this reason, we first try to drop those ~20 columns and then add those again using 'alter table add' , so that they will be created towards the end of the row. Any better/faster approach through which we can achieve this?
Regards
Pap
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 08 2022 - 06:19:20 CEST