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

From: Jonathan Lewis <>
Date: Thu, 15 Dec 2011 16:44:49 -0000
Message-ID: <B0372C0A516D470B9044A998E9CE1567_at_Primary>

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:

  1. how many times the check has to take place (there is a key caching mechanism, I believe, to reduce the checking)
  2. how well the PK gets cached in the buffer cache
  3. 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


Jonathan Lewis
Oracle Core (Apress 2011)

  • Original Message ----- From: "Rich" <> To: "Oracle-L Freelists" <> 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:

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?


-- Received on Thu Dec 15 2011 - 10:44:49 CST

Original text of this message