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

From: Elliott, Patrick <patrick.elliott_at_medtronic.com>
Date: Wed, 6 Feb 2008 12:33:49 -0600
Message-ID: <3B8B6A1700202C43A89D61CE495C894E0D22D910D1@MSPM1BMSGM103.ent.core.medtronic.com>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Finn Jorgensen Sent: Wednesday, February 06, 2008 12:22 PM To: post.ethan_at_gmail.com
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_at_gmail.com<mailto:post.ethan_at_gmail.com>> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 06 2008 - 12:33:49 CST

Original text of this message