Re: ALTER TABLE ADD columns in parallel?

From: Mark Bobak <>
Date: Wed, 5 Mar 2014 16:11:19 +0000
Message-ID: <>

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.


On 3/5/14, 10:44 AM, "Rich Jesse" <> wrote:

>Hey all,
>In, our ERP migration is adding 14 columns with default values
>to a
>populated table:
>ALTER TABLE erptbl ADD (newcol1 NCHAR(1) DEFAULT ' ',...
>-- This is running single-threaded, taking 4+ hours over the 16.5M rows
>-- I tried altering the table with PARALLEL=8 (server has 16 logical CPUs)
>and even ALTER SESSION FORCE PARALLEL DDL, but no luck.
>-- There are 254 columns with a mix of NCHAR and NUMBER (no LOBs, which
>could prevent parallel).
>-- The ERP software (JDEdwards) is generating the SQL, and AFAIK this
>be changed.
>I've been trying to search on MOS, but even though they thankfully fixed
>right-click-on-link issue, it only lets me look at about the first 60
>results of a query. I've also scanned the docs, but nothing seems to

Received on Wed Mar 05 2014 - 17:11:19 CET

Original text of this message