Alter add column Slowness
From: Pap <oracle.developer35_at_gmail.com>
Date: Sun, 4 Apr 2021 00:39:06 +0530
Message-ID: <CAEjw_fhc1H2OEv0weOZyrEqmHPKWcubPQVEOoFWZLYzDMR-o3g_at_mail.gmail.com>
Hello Listers, We are using version 11.2.0.4 of Oracle and its exadata X5. And my thought was that the "Alter table add column "will happen faster here because of the "fast column add" feature as in this version Oracle will just update the data dictionary but will not update each and every value of the column. A third party tool submitted one ALTER statement to the database , something as below on a 100million rows table and the statement is stuck in the first statement itself, and we see from longops its scanning the whole table. So wanted to understand , if that "fast column add" only works for "default not null"?
Date: Sun, 4 Apr 2021 00:39:06 +0530
Message-ID: <CAEjw_fhc1H2OEv0weOZyrEqmHPKWcubPQVEOoFWZLYzDMR-o3g_at_mail.gmail.com>
Hello Listers, We are using version 11.2.0.4 of Oracle and its exadata X5. And my thought was that the "Alter table add column "will happen faster here because of the "fast column add" feature as in this version Oracle will just update the data dictionary but will not update each and every value of the column. A third party tool submitted one ALTER statement to the database , something as below on a 100million rows table and the statement is stuck in the first statement itself, and we see from longops its scanning the whole table. So wanted to understand , if that "fast column add" only works for "default not null"?
ALTER TABLE TAB1 ADD (C1 NUMBER (2,0) DEFAULT 0 null);
ALTER TABLE TAB1 MODIFY (C1 NUMBER (2,0) DEFAULT 0 not null);
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 03 2021 - 21:09:06 CEST