Re: ALTER TABLE ADD columns in parallel?

From: Mark Bobak <Mark.Bobak_at_proquest.com>
Date: Wed, 5 Mar 2014 18:05:12 +0000
Message-ID: <CF3CCE08.54ED8%Mark.Bobak_at_ProQuest.com>

Yep…I got so focused on “make add column go faster”, I overlooked a few *minor* details there. Sorry.

Actually, there’s a way to do this w/o using “NOT NULL”, but, it still may not help, depending on how much you can influence the code that’s generated.

If you can separate the column add and the default, it will run w/ DDL-only update, and not require the NOT NULL.

SQL> set timing on
SQL> alter table testme add(blah2 nchar(1));

Table altered.

Elapsed: 00:00:00.01
SQL> alter table testme modify(blah2 default ' ');

Table altered.

Elapsed: 00:00:00.01
SQL> select count(*) From testme;

  COUNT(*)

---

 100000000

1 row selected.

Elapsed: 00:01:15.87

Again, not sure if it will help in your specific case, though….

-Mark

On 3/5/14, 12:04 PM, "Rich Jesse" <rjoralist3_at_society.servebeer.com> wrote:

>Mark replies:
>
>> Try this instead:
>> Alter table erptbl add (newcol1 nchar(1) default ΠΠnot null);
>>
>> I think you¹ll find that if you add the ŒNOT NULL¹, it becomes DDL only
>> operation, and completes w/ sub second response.
>
>Perhaps, but a NOT NULL constraint and DEFAULT value are very different.
>The NOT NULL will error out when violated whereas the DEFAULT value will
>just fill in the gap.
>
>And I'm not able to modify the SQL...
>
>Thanks for the feedback!
>
>Rich
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>

��i��0���zX���+��n��{�+i�^ Received on Wed Mar 05 2014 - 19:05:12 CET