ALTER TABLE ADD columns in parallel?

From: Rich Jesse <rjoralist3_at_society.servebeer.com>
Date: Wed, 5 Mar 2014 09:44:01 -0600 (CST)
Message-ID: <71c35d76bcbf603951ce749d0c98e4ba.squirrel_at_society.servebeer.com>



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
Received on Wed Mar 05 2014 - 16:44:01 CET

Original text of this message