What is the partition type involved here?
One option you can load data by partition into intermediary table(with new
columns) and exchange. This way you can run parallel sessions for each
partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db to
prod using data-pump?
Karth
Sent from my IPhone
On Jul 23, 2018, at 7:52 PM, Woody McKay <woody.mckay@xxxxxxxxx> wrote:
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a few
billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about
13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE LOGGING.
Guessing alter table nologging wouldn't do much either. Can't exchange from
old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but the
production DBA's are worried about chained-rows, fragmentation, possible
explain plan changes, etc. I'm not sure if they are justified in their
thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody
