Re: Huge Table Insert - take long time?

Thanks Brent - I'll have to check that out...

On Mon, Jul 23, 2018 at 8:10 PM Brent Day <coloradodba_at_gmail.com> wrote:

> Have you looked at DBMS_REDEFINITION? Your could possibly reorg while the
> app remains up.
>
> On Mon, Jul 23, 2018, 5:53 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