Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Partition Exchange Loading (PEL)

Re: Partition Exchange Loading (PEL)

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 30 Jun 2002 11:24:31 +0100
Message-ID: <1025433271.19988.2.nnrp-14.9e984b29@news.demon.co.uk>

Is PEL a genuine Oracle acronym ?!
I guess that means it can go into an OCP question.

Good for you for testing. There are always special cases, and you should never assume that something is true just because it is written in a manual.

A couple of points to consider.

    500,000 rows is not a very large amount of data.

    Your times are so small that the variations are     almost irrelevant.

    Was your SQL*Load a direct path, nologging load ?     The index create seems a little slow.

    Parallel 8 on index create is probably a bad idea     on a smallish data set like this. The wasted space     of 8 half empty extents is probably significant, and     the cost of starting up the slaves is possibly a     significant fraction of the total time.

    Were you running in ARCHIVELOG or NOARCHIVELOG     mode ? There are some operations where Oracle generates     redo if you are running in ARCHIVELOG mode, but not when     you are running in NOARCHIVELOG mode. Occasionally     this means something that runs quickly on developments     runs much more slowly on production.

    One index is not very many - one of the notional benefits     of 'PEL' is that you can create all the indexes at the same     time

    Oracle is always introducing new optimisation strategies     to 'ordinary' processing that bring performance closer to     the levels achievable by special tools. (For example the     strategy of sorting and bulk inserting index entries after     an array insert). This does mean an array insert into an     empty indexed table is a very special case of array inserts     as far as performance goes. (But then, the spare table     for 'PEL' is probably going to be empty anyway).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

SerGioGio wrote in message ...

>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 Sun Jun 30 2002 - 05:24:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US