Re: does unique constraints hurt insert performance

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 13 Aug 2006 13:55:18 -0700
Message-ID: <1155505382.545091@bubbleator.drizzle.com>

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

You indicate that it appears a unique index is more expensive than a unique constraint. I had not been aware of the previously but have always advocating NEVER building unique indexes (as opposed to constraints) just because of the difference in how to appear in the data dictionary.

Also, you mention the cost of a rollback being higher with an array insert but I wonder whether that would be true if one were to use DBMS_ERRLOG (yes it is 10g) and do the insert with the LOG ERRORS syntax.

What is interesting is that almost everyone has interpreted the OP's question in a different manner. I assume the question to be the cost to the system while the index was being built. Apparently a misread.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sun Aug 13 2006 - 15:55:18 CDT