Re: Alter add column Slowness

Thank you very much Andy and Jonathan.

So it means in current version if we would have altered the column to default not null in one statement that would have happened as column fast add. Thanks for the guidance.

On Sun, 4 Apr 2021, 4:13 am Andy Sayer, <andysayer_at_gmail.com> wrote:

> 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