Re: question about impact of table partinioning on the insert statements for the table

The following is a brief note on some experiments I have done to get a rough handle on the overhead of Oracle determining the partition dynamically.

There is an effect due to the complexity of the partitioning (number of columns, type of column, number of partitions), but realistically this doesn't seem to be significant.

The main controlling factors appear to be:

    size of array inserts
    randomness of rows relative to the partitioning columns

I am sure that there are plenty of carefully controlled experiments I should do to pin things down carefully, but as a rough guideline when using relatively small numbers of partitions and a simple partitioning strategy.

1. Single row inserts, sorted or random data Overhead is about 7% or 8%. The 'low' number is, I believe, largely because single row inserts are inherently so expensive
2. Array inserts (arraysize 90 in my case) Data completely random with respect to partition: Overhead is about 25%
3. Array inserts Data sorted so that all data for a given partition is collated Overhead is virtually zero.

I believe that when processing array inserts oracle must have a cunning short-cut which says ' check to see if the current row belongs to the same partition as the last row' before calling the normal entry point for the partition processing.

--
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

I have a spelling chequer
It came with my pea sea
It plainly marques for my revue
Miss steaks eye cannot sea

I've run this poem threw it
I'm shore your pleased to no
Its letter perfect in it's weigh
My chequer tolled me so

prabhakarsrini_at_my-dejanews.com wrote in message <7bektf$sdt$1_at_nnrp1.dejanews.com>...
>Hi! Everybody,
> My name is Srini and I need somebody to clarify to me
>about the impact if any that oracle's table partioning will have in
>the insertions of rows to that table in a batch or online application.
> I am really concerned about the commit time and other
performance
>issues?...
>
Received on Tue Mar 02 1999 - 15:15:25 CST