Re: does unique constraints hurt insert performance

sunh11373_at_gmail.com wrote:
> 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

Oracle version number?
Partitioning option?
Local or global index?

Rather than doing ALTER TABLE ADD CONSTRAINT ... use the DBMS_REDEFINITION built-in package. There is a demo in Morgan's Library at www.psoug.org.

But definitely try it first on a test box.

Also: You might want to run DBMS_SPACE.CREATE_INDEX_COST first to determine the space impact. There is a demo of this in the library as well.

-- 
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