Re: Huge Table Insert - take long time?

That's an interesting thought Karth. Most are partitioned by range (date) and interval (daily or weekly) subpartitions.

Gonna chew on this one - thanks

On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan < keyantech_at_gmail.com> wrote:

> 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_at_gmail.com> 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
>
>
>

-- 
Sincerely,

WoodyMcKay

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