Re: Not null and check constraints

The impact on performance for inserts and updates should be negligible - at least compared to the cost of doing the check in your own code. Presumably if the check constraint is needed then it has to be checked somewhere.

There is a potential for a dramatic performance impact if you omit NOT NULL constraints which you know to be true, however. There are some high-efficiency access paths that Oracle can use if a column is declared as NOT NULL that it cannot use if the column is not so declared.

Also, though I haven't checked the real benefit of this, and I suspect it may not be true anyway, the manuals tell us that bitmap indexes will be smaller if all the NOT NULL columns in a table are declared properly.

Because __trailing__ null columns in a row do not need a column length byte, you may find that suitable ordering of columns to push the more-frequently null columns to the end of the row may reduce the physical storage of a table. However, this may be offset by the CPU cost if many of your queries then check the last column of the table - and therefore have to walk the entire row to find that column value. In "!high-power" systems, there is some benefit in getting exactly the right column ordering, but it isn't a trivial exercise to work out what that order is.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Daud wrote in message ...


>Hi

>

>Will there be performance problem (during insert, update etc.) if I

>have many not null and check constraints in a table? What if the table

>has millions of records in it? What's the impact of having those

>constraints? And will there also be performance problem if I do not

>group the 'not null' and null columns?

>

>rgds

>Daud11