Re: ALTER TABLE ADD columns in parallel?

From: Mark Bobak <Mark.Bobak_at_proquest.com>
Date: Wed, 5 Mar 2014 16:11:19 +0000
Message-ID: <CF3CB698.54EBF%Mark.Bobak_at_ProQuest.com>

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.

-Mark

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

>Hey all,
>
>In 11.2.0.3, 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
>(~42GB).
>
>-- 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
>can't
>be changed.
>
>I've been trying to search on MOS, but even though they thankfully fixed
>the
>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
>stick
>out.
>
>Thoughts?
>
>TIA!
>Rich
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>

--
http://www.freelists.org/webpage/oracle-l

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