Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Partition Exchange Loading (PEL)
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 ...Received on Sun Jun 30 2002 - 05:24:31 CDT
>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