Re: Alter add column Slowness

Just to add: Oracle upgraded the column fast add in 12.1 to include nullable columns. If you do want the column to be added as nullable, it’s time to upgrade.

If you (in 11.2.0.4) add the column as not nullable (fast) and then make the column nullable, it will be a fast metadata operation, but you will end up hitting bugs - I don’t have the notes to hand but I believe this causes data corruption on upgrade.

On Sat, 3 Apr 2021 at 20:56, Jonathan Lewis <jlewisoracle_at_gmail.com> wrote:

> 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