Partition Exchange Loading (PEL)

From: SerGioGio <serge.malonga_at_supelec.fr>
Date: 27 Jun 2002 07:20:15 -0700
Message-ID: <e42147c6.0206270620.74b3bec6@posting.google.com>

Hello !

I am a newbie in the world of databases and datawarehouses. I have read some documentation about the Partition Exchange Loading (PEL) promoted by Oracle for 9i, and I have been through some testing.

I have a table of more than 180 million rows (of 400 bytes each) partitioned by day. Every day, 500 000 rows are loaded into a new partition, and 500 000 older rows are removed (ie a whole partition is deleted). All indexes are LOCAL. This is the classic "rolling window" scenario.

The PEL consists in :
* creating a new partition

• creating a stage table
• loading data in the stage table with SQL*Loader in full speed (no indexes, no intergrity constraints, etc) [DIRECT=TRUE, PARALLEL]
• creating the indexes on the staging table [NOLOGGING PARALLEL 8]
• "exchanging" the staging table with the new partition created at the beginning [WITHOUT VALIDATION INCLUDING INDEXES]

As far as I understand, the *ONLY* advantage of PEL is that no index maintenance is done while inserting new data, BUT indexes are recreated at the end of the loading.
I undestand that this means that recreating indexes is faster than maintaining them. Is this true ?

Now, about the experiments I made :
one *empty* table with one index. The fact that the table is empty should not influence the results, as far as I understand.

1. I tried to load one partition (500.000 rows) in the empty table using the PEL (staging table, index recreation, etc) : 11 s loading and 24 s index creation -- it is fast !
2. I tried to load one partition (500.000 rows) in the empty table using only SQL LOADER (target is THE table, no staging table, hence no index recreation, but index maintenance automatically done by ORACLE) : 19 s loading -- it is even faster, and much easier to achieve !

I understand that this is a very particular case I am talking about, but I would like to hear your opinions about the experiments and the results.
To me not using PEL seems faster and easier. But I am a newbie and I would like to have experimented people thoughts.

Thanks in advance !

SerGioGio Received on Thu Jun 27 2002 - 09:20:15 CDT