Choose the correct data types and size to: – Avoid NULLs – Improve performance – Protect data – Use data compression where appropriate
I don't understand why I need to avoid NULL. Please explain.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
While I do use NULL columns, there is overhead. The Oracle documentation you retrieved this short list should explain if you read further.
There are case where NULL indicates issues with data types and/or just having the column.
It is always possible to normalize NULL columns out of the database. It may not always be appropriate to do so.
I do try to put NULL columns last. I've posted on column order previously.
NULLs is very low on my list of optimizations. I prefer to say "use
NOT NULL wherever appropriate". That implies that if you need
NULL, go ahead and use it. I do find in my own tables that I rarely have a use case for
See Rick's RoTs for a longer list of recommendations; they are aimed at MySQL, and come from years of optimization experience with MySQL/MariaDB.
That Oracle list leaves out two important issues:
innodb_buffer_pool_sizeto about 70% of available RAM. Details.
NULL has many possible 'meanings', such as:
It is probably more efficient to use
NULL than to have another column. On the other hand, it may be better to pick a special value for null-like information, such as 0 or -1 or:
ENUM('decline-to-state', 'male', 'female') NOT NULL
ENUM('male', 'female') NULL
As for Normalization, I say "normalize, but do not 'over-normalize'". In particular, do not normalize any "continuous" values, such as