Question on tunning while INSERT SELECT

[balajiyes]

Hi,

As part of my new requirement, I need to load 360 million records using INSERT SELECT with append mode as single transaction and COMMIT once completed.

I have been asked not to use partition swap, because data will be lost if the partition swap fails. And not allowed to include NOLOGGING as the database would be in ARCHIEVE mode.

I need your expert brains. I need your help to speed up the performance of the INSERT SELECT.

Technology: PLSQL
Oracle Version: 11G R2
Server: M5000 + 400 GB Flash

Note: As of now, we do not have a 11g environment.

Many thanks in advance.

Regards,

[PAVB]

Quick question... do you have enough space on UNDOTBSx to handle a 360 million rows transaction?

Assuming you do, and not starting a discussion on imposed limitations, is this load targetting different partitions in affected table? if this is the case you may try to convince your boss to let you run several load processes in parallel, one for each affected partition. Latelly we have done this to reload a 1.7 billion rows table with amazing results.

Last but not least... are you allowed to drop indexes on target table?

[balajiyes]

Thanks Paul

We have been told there will be loads of UNDO space.

We do not have any indexes.

Entire 360 million belongs to one processing day. This will go to 1 single partition on the target table. That is why I thought swap partition is the best option.

Is there anything we can do like adding new HINTS in 11g, Server configuration, etc.

Thanks and Regards

[PAVB]

You are absolutely correct - partition swap will do it instantly.

By the way, since parition swap is a metadata operation I really cannot see how it may fail. As a safe net and to make your boss feel more secure you may want to propose to take an expdp of the 360M rows table previous to performing the operation.

[balajiyes]

Thanks Paul

Thanks and Regards