Re: How much time required to add constraint(s)

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 15 Dec 2011 16:44:49 -0000

Technically you have to check the primary/unique key on the parent table 
for every row in the child table.
The minimum is a full tablescan of the child table.
Then you can expect to do 3 (or 4 depending on height of index) LIOs in the 
index - but the overall time depends on:

a) how many times the check has to take place (there is a key caching 
mechanism, I believe, to reduce the checking)
b) how well the PK gets cached in the buffer cache
c) how much read-consistency work has to take place

Bear in mind you can do this in two steps:

add the constraint as "enable  novalidate" - which will be quick as it 
doesn't check existing data
alter the constraint to validate it - which does a lot of work, but doesn't 
have to lock the table while it takes place

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com
Oracle Core (Apress 2011)
http://www.apress.com/9781430239543


----- Original Message ----- 
From: "Rich" <richa03@xxxxxxxxx>
To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, December 15, 2011 4:37 PM
Subject: How much time required to add constraint(s)


Hi list,
I'm trying to determine how much time will be required to build a
constraint - something like:
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <NAME> FOREIGN KEY (<FK_NAME>)
REFERENCES <REF_TABLE_NAME> (<REF_COL_NAME) ENABLE;

Testing this, I don't see any operation in v$session_longops, however
the test runs for hours.
There are many of these and we have limited amounts of time to do them
- we can do them in batches over time, however, we can't exceed our
windows due to performance.

I also don't see anything in v$sql_plan for this SQL_ID...

How, exactly, does Oracle build a constraint?
Ie, what plan/operations does it use.
Is there any way I can estimate time (blocks read, etc.) prior to
building the constraint?

Thanks,
Rich
--
//www.freelists.org/webpage/oracle-l


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


Other related posts: