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.
Pat
________________________________
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.
Finn
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
way.
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).
Thanks,
Ethan
___________________________________________________________________________________________________
CONFIDENTIALITY AND PRIVACY NOTICE
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
records.
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:
http://emaildisclaimer.medtronic.com
Other related posts:
