RE: Optimizer advantage for NOT NULL Check constraints on PK's?

  • From: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>
  • To: "finn.oracledba@xxxxxxxxx" <finn.oracledba@xxxxxxxxx>, "post.ethan@xxxxxxxxx" <post.ethan@xxxxxxxxx>
  • Date: Wed, 6 Feb 2008 12:33:49 -0600

You should be able to add it with the NOVALIDATE clause because it is already 
checked by the PK.  Then it will be instantaneous.


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Finn Jorgensen
Sent: Wednesday, February 06, 2008 12:22 PM
To: post.ethan@xxxxxxxxx
Cc: oracle-l
Subject: Re: Optimizer advantage for NOT NULL Check constraints on PK's?

The PK constraint will not allow NULLs to be inserted into the column. The 
columns really should have been defined with a NOT NULL constraint from the get 
go. I don't see a harm in adding the NN constraints now, except if it's a large 
table it will take some time for the DDL to finish since it has to check that 
all rows actually have values in the columns being changed and the table is 
locked while that happens.


On 2/6/08, Ethan Post <post.ethan@xxxxxxxxx<mailto:post.ethan@xxxxxxxxx>> wrote:

We have some databases in which the original columns were added with a NOT NULL 
constraint and a PK was added in another statement. Other databases only have 
the PK. When you compare these two databases you get a variation because the 
former has 'C' type constraint on the PK column as well as the PK constraint.

I am tempted to go add NOT NULL constraints on all PK columns as a rule or 
modify the columns to "foo data_type null" since the rules will still be 
enforced by the PK so that my db compares sync up. This is 9i and 10g by the 

I would guess that the a Check constraint and PK are the same thing to to the 
optimizer and it does not matter one way or the other. Can anyone confirm if 
this in fact that case? Any issue with removing or adding these across the 
board (assuming only on PK columns).


Information transmitted by this email is proprietary to Medtronic and is 
intended for use only by the individual or entity to which it is addressed, and 
may contain information that is private, privileged, confidential or exempt 
from disclosure under applicable law. If you are not the intended recipient or 
it appears that this mail has been forwarded to you without proper authority, 
you are notified that any use or dissemination of this information in any 
manner is strictly prohibited. In such cases, please delete this mail from your 

To view this notice in other languages you can either select the following link 
or manually copy and paste the link into the address bar of a web browser:

Other related posts: