Re: Partitoned Table Insert Performance

My last set of test results is a little out of date, but here's an idea to check.

Inserting single rows:

    partitioned key insert HAD ca. 50% overhead

Array Inserts sorted by partition key to get lots of adjacent rows in the same partition

    virtually no overhead

Array inserts randomised to maximise the probability of adjacent inserts being to different partitions

    Overhead ca. 50%

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 02 May 2002 17:35

|I am in the process of implementing partitioning on some existing
tables. I
|have been asked by management to evaluate the performance impacts of
the
|changes. I am aware of many of the performance advantages of
partitioning:
|partition pruning, partition-wise joins and parallel data loads. What
I am
|concerned about is the additional overhead of inserting data into a
|partitioned table. What sort of overhead is associated with
partitioned
|table inserts? Does determining the correct partition slow
insertions? We
|are utilizing only range partitions, so hash value computations
should not
|be a factor. Our application is very insertion intensive. One of the
|operations that performs insertions does so synchronously, so any
decrease
|in insertion performance would be quite visible. Do you have any
thoughts or
|experiences with this issue. Any performance tests on two similar
tables,
|one partitioned and the other not? Any input would be appreciated.
Thanks.
|Erik

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).