RE: ALTER TABLE ADD columns in parallel?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 5 Mar 2014 12:03:57 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark Bobak
Sent: Wednesday, March 05, 2014 11:11 AM
To: rjoralist3@xxxxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxx> 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
>
>--
>//www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
