RE: Question about unique constraint
Date: Tue, 28 Jun 2016 05:12:55 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D90150332733_at_exmbx05.thus.corp>
Assuming you're running 11g -
If you do 'create index online' you'll take a mode 2 lock on the table to build the index, then (briefly, probably) a mode 4 (waiting for DML on the table to commit and block new DML locks) to bring the index up to date with any changes made during the build. This MAY take some time (a) to get the lock and (b) to bring the index up to date.
Adding the PK should take no time in novalidate mode, but won't add the NOT NULL declaration to the column altering the PK to validate will lock the table in mode 2 (no interference with DML) but will do a tablescan to check for NOT NULL, and then modify the column to NOT NULL.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Michael Cunningham [napacunningham_at_gmail.com] Sent: 27 June 2016 19:44
To: oracle-l_at_freelists org
Subject: Question about unique constraint
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
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 28 2016 - 07:12:55 CEST