Question: 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](https://i.stack.imgur.com/R9YBQ.png)](https://i.stack.imgur.com/R9YBQ.png)

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

Answer: 
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](https://stackoverflow.com/questions/4939735/re-order-columns-of-table-in-oracle) previously.
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](http://mysql.rjweb.org/doc.php/ricksrots) 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](http://mysql.rjweb.org/doc.php/memory).
* [How to](http://mysql.rjweb.org/doc.php/index_cookbook_mysql) '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`.