Re: does unique constraints hurt insert performance

Jonathan Lewis wrote:
>
> There is a measure of truth in the comment. If you are inserting
> using single-row processing, then each insert will have to pin
> the index leaf blocks from root to leaf very briefly - which means
> the root block will get hit very hard if these inserts are coming from
> multiple concurrent processes and you are likely to see some latch
> contention on the latch covering the root block. (PL/SQL is
> a special case, though).
>
> If the data is arriving in an order that causes very random reads
> of the index the latching is likely to be relatively insignificant compared
> to the possible physical read load.
>
> If the data is arriving in sorted order, then the time lost on buffer
> busy waits on the leaf blocks is likely to be the major time waster.
>

Hi,

Thanks, I think this is the answer I am looking for.

BTW, in my case, we use PL/SQL (stored procedure called from Java). The data is inserted one row at a time and is very random. Received on Sun Aug 13 2006 - 23:45:19 CDT