Re: does unique constraints hurt insert performance

"sunh11373" <sunh11373_at_gmail.com> wrote in message news:1155498636.143543.100730_at_m79g2000cwm.googlegroups.com...
>
> If I have a large table with a column defined as "unqiue index", will
> it cause performance problem in a high inserting rate environment(all
> single insert)? I heard from someone that if the index is "unique", the
> insert will have to "lock" the top node of the index tree to update the
> index. Thus it will cause a lot of latch contention under hight
> inserting case. But I suspect this is not the case. It might be true
> for some type of databases, but I don't think Oracle does this. Can
> someone offer some insights here?
>
>
> Thanks
>
>

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.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html