Re: Importing and indexing large tables

In article <334774B1.6B49_at_hk.super.net>, Michael Ho wrote:
>Alan Jeskins-Powell wrote:
>>
>> Consider this: 12 tables, average 3 million rows and 4 indexes per table.
>> It has become impossible for us to export/import this data in a weekend
>> (the only free processing time we have) given the techniques we are
>> currently using (std.Oracle exp/imp)
>> Does anyone have any ideas on how we can spped the process up so that we
>> can a) fit the exp/import into a working weekend and b) still allow time
>> for harware/software upgrades as well.
>
>1. Don't do that, I can't think a reason you need to do exp and imp per
>week.
>
>2. Try exp/imp using NATIVE mode.
>
>3. Try disk mirroring the server and imp/exp the mirrored server.

To easiest way to speed-up imp/exp is to generate the 'single task' versions of these executables. WARNING: If you use the single task version you must be sure that NOONE IS ACCESSING THE DATABASE, otherwise there is a slight chance of corruption!!!!!!

Also, ensure that the tables are created with an initial extent which is large enough to accomodate all of the data - dynamic extending of tables is quite a performance hit.

Also, ensure that the indexes for the tables get created AFTER the data is loaded. This will give performance and fragmentation benefits.

Single Task Info (for Unix)

cd $ORACLE_HOME/rdbms/lib
make -f oracle.mk singletask

This will generate: impst expst sqldbast sqlldrst

Copy the programs to $ORACLE_HOME/bin but ensure that only the oracle owner has access to them

Using these instead of the normal programs will give app. 30% performance improvement due to direct access to the SGA & other things.

regards

Neil Chandler. Received on Tue Apr 08 1997 - 00:00:00 CDT