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

From: Finn Jorgensen <finn.oracledba_at_gmail.com>
Date: Wed, 6 Feb 2008 13:21:44 -0500
Message-ID: <74f79c6b0802061021h2c9be411k521a2ad5df884990@mail.gmail.com>


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> 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
>

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

Original text of this message