Oracle recommends:

Choose the correct data types and size to:
– Avoid NULLs
– Improve performance
– Protect data
– Use data compression where appropriate

enter image description here

I don't understand why I need to avoid NULL. Please explain.

  • 1
    Funny to see this coming from Oracle, where NULLs and empty strings are indistinguishable. – mustaccio Jul 17 '16 at 19:03

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.

  • Consider PHONE_NUMBER NUMBER(15): This will likely have a formatted phone number column, and may be null for numbers like 1-800-CALL-NUM.
  • Other columns may be NULL because you are unable to gather the data require to fill them. Consider dropping the columns if is unlikely you will be gather the data.
  • NULL columns may belong to a sub-type. It may be appropriate to have a related table containing type related data. This would have an optional 1 to 1 (1 to (0,1)) relationship.
  • NULL columns are problematic for indexes.
  • NULL columns may be state related, such as DATE_SHIPPED on a order. It would be appropriate for this to be NULL before the order is shipped. However, it would be inappropriate for a has_shipped indicator column to be NULL.

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.

| improve this answer | |

Avoiding 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 NULL.

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:

  • Set innodb_buffer_pool_size to about 70% of available RAM. Details.
  • How to 'create the optimal indexes'.

NULL has many possible 'meanings', such as:

  • Not yet known
  • Optional
  • Removed

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

instead of

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 FLOAT or DATE.

| improve this answer | |
  • Aren't ENUM types evil? – Vérace Jul 18 '16 at 0:18
  • @Vérace - we agree to disagree? – Rick James Jul 18 '16 at 4:55

Not the answer you're looking for? Browse other questions tagged or ask your own question.