Re: Alter add column Slowness

SQL Reference manual for 11.2
Alter table command - add column clause

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_3001.htm#i2198241

If you specify the DEFAULT clause for a NOT NULL column, then the default value is stored as metadata but the column itself is not populated with data. However, subsequent queries that specify the new column are rewritten so that the default value is returned in the result set.

If you specify the DEFAULT clause for a nullable column, then the default value is added to existing rows as part of this ALTER TABLE statement, and any update triggers defined on the table are fired. This behavior also results if you change a NOT NULL column with a default value to be nullable.

Regards
Jonathan Lewis

On Sat, 3 Apr 2021 at 20:09, Pap <oracle.developer35_at_gmail.com> wrote:

> 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-l