RE: ALTER TABLE ADD columns in parallel?
Date: Wed, 5 Mar 2014 12:03:57 -0500
Message-ID: <000001cf3894$e6775810$b3660830$_at_rsiz.com>
Following up, while Mr. Bobak's suggestion (plugging in the correct bits for the characters that arrive fubar'd at least to me) is an effective way to add the columns which should be transparent to the software using it (but possibly not "supported" if you read the fine print regarding the JDE software - I'm not sure),
you did mention:
>
>-- The ERP software (JDEdwards) is generating the SQL, and AFAIK this
>can't be changed.
>
Now I'm not *sure* whether that is a dynamic module. IF you have a test bed, I would suggest trying Mark's suggestion pre-emptively to see whether the update looks at the table structure, sees that it already has this structure, skips that bit of the update, and continues on. Some update generators of configurable off the shelf products do exactly this, since one or more independent one off patches may need the same schema updates for the patch to work but do not insist that either of the patches is a prerequisite to the other.
That *may* work, depending on how the update software generator is built. Or it may horribly stop with a message something like "you've already applied this update, you idiot" so be certain that your finesse of the situation does not break the update. It could also fail if the patch references the table with a * prior to the add column bit and thereby something else goes horribly wrong. (That would be a bad way to build a patch, yet that might nevertheless happen.)
IF the above does NOT work, AND IF no other surgery or reference by row contents is made to the tables having columns added during the patch, THEN you might swap in place a one row table for the one having columns added and then manually follow Mark's suggestion after the patch finishes (having swapped your real table back in place.)
Even if the patch references the "columns being added" table(s) during the patch, IF the patch applier has stop and restart granularity, you might interrupt it at the appropriate points (after the columns are added to your tiny stand-in) but before the patch references or attempts to further change that table, and then swap back in the real table.
Take all this with a large grain of salt, in the context that you must not break your ERP package, so technically correct workarounds without the full context of the software you are running might still fail in some regard.
Good luck,
mwf
PS: you mentioned 254 columns in the table. Is that before or after the column add(s)? When you exceed 254 or 255 columns (I'm not looking that up at the moment), then you get multiple row pieces. If you cross the boundary where multiple row pieces result, hilarity may result. I have not experimented with what ensues adding columns via the default value in the dictionary method either when firing the add column statement, or later when perhaps a column is update to a non-default value. I can imagine quite a bit of fairly nasty stuff, none of which should actually break, but which might be slow and expensive, if you add size to a bunch of new columns in fairly full blocks. Again, good luck.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Mark Bobak
Sent: Wednesday, March 05, 2014 11:11 AM
To: rjoralist3_at_society.servebeer.com; oracle-l_at_freelists.org
Subject: Re: ALTER TABLE ADD columns in parallel?
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 05 2014 - 18:03:57 CET