Re: Huge Table Insert - take long time?

Thanks Hemant. All new columns are NOT NULL and get data with updated app code. Older recs may not have data for any new columns.

Didn't realize that about APPEND avoiding undo even if FORCE LOGGING. Thanks

On Mon, Jul 23, 2018 at 8:19 PM Hemant K Chitale <hemantkchitale_at_gmail.com> wrote:

> You mention "additional columns".
>
> Are you using transforms, functions, joins to generate the values for the
> new columns ?
> Maybe these operations are taking time ?
>
> APPEND avoids undo generation, so it does reduce redo generation even if
> you have FORCE LOGGING.
> NOLOGGING doesn't help in the presence of FORCE LOGGING.
>
> On Tue, 24 Jul 2018, 07:53 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