Re: does unique constraints hurt insert performance

Hi,

Thanks for everyone here that offered your advises, especially J.L. (BTW, I have enjoyed reading some of your books on Oracle performance).  I haven't expected to get so many responses for a seemingly "simple" question. In my first post I tried to make some context for the question. But it turned out many prople are distracted and did not touch the real question I was asking. It is my fault and let me try to restate my question here:

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

Jonathan Lewis wrote:
> <sunh11373_at_gmail.com> wrote in message
> news:1155426742.867263.327670_at_p79g2000cwp.googlegroups.com...
> > Hi,
> >
> > If I have a table which has 1 billion rows (data in each row is
> > relatively small), delcare one column (not the primary key column) as
> > unique, will this cause big problem for inserting?
> > Assume the inserting rate is 500/sec and the possible duplication is
> > 0.001%.
> >
> > Thanks
> >
>
>
> That's a question worth a bit of discussion,
> so I've put jotted down a few thoughts in a short
> article on my website.
>
> http://www.jlcomp.demon.co.uk/unique_constraint.html
>
>
> --
> 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
Received on Sun Aug 13 2006 - 14:50:36 CDT