Re: Question about unique constraint

I've used this approach a number of times, including on multi-TB tables, and it's worked very very well. I usually validate the constraint once the missing data has been fully populated, which is a non-blocking operation (since Oracle already knows that no new data can be inserted which violates the constraint in ENABLE NOVALIDATE mode), but this step may not be required for a data warehouse scenario.

alter table dba6975_test modify constraint dba6975_test_CPK validate;

On Mon, Jun 27, 2016 at 1:44 PM, Michael Cunningham < napacunningham_at_gmail.com> wrote:

> Posting this for a colleague.
>
>
> Hello everyone.
> I have various tables of fairly large sizes from 100GB till 3TB that I
> need to add primary key constraint in such way that clients can continue
> modifying tables. Locking tables is not an option. One of the approach I am
> considering is to build unique index online first then add constraints
> using index novalidate. Will this approach work? Are there side effects of
> adding constraints using novalidate option.
>
> CREATE UNIQUE INDEX dba6975_test_PK ON dba6975_test(test_id) LOGGING
> TABLESPACE INDXTBS1 ONLINE;
>
> alter table dba6975_test ADD CONSTRAINT dba6975_test_CPK primary
> key(test_id) using index dba6975_test_IDX1 novalidate
>
> Suggestions are greatly appreciated.
>
> --
> Michael Cunningham
>

-- 
"In the beginning the Universe was created. This has made a lot of people
very angry and been widely regarded as a bad move." -Douglas Adams

--
http://www.freelists.org/webpage/oracle-l