Oracle: Migrating a col. with values, nulls and 0s

Question

I'm migrating data from one db to another.

I have a numeric col. that contains some values, some 0s and some nulls. My gut feeling is to convert all the 0s to NULLs. This will store an optional, user-provided number.

Any reason NOT to convert all the 0s to NULLs?

Answer 1

There are issues with indexing NULLs (in that generally they aren't).

If you mean 0, use 0. Using NULL instead will change your sort order and affect arithmetic (and comparison). NULLs are used to indicate there is no value. Using them as a replacement for 0 (which is a value) is a good example of micro-optimization driving your design rather than.. the design.

Answer 2

One reason not to convert zeros to null is that they mean different things mathematically.

1. If I have the numbers (0,10,20), the average of those is 10. If I convert the zero to null, the average becomes 15.
2. If I have the numbers 5 and 0, added together they make 5. Turn the 0 to a null and added together they make NULL.
3. The default ordering of null,-5 and 10 is (-5, 10, NULL) unless you use NULLS FIRST in which case you get (NULL, -5, 10). Obviously the ordering with zero is (-5, 0, 10).

So if these are numbers, used for numeric functions, then you need to use NULLs and zeros in accordance with the application requirements (which you don't give).

Maybe if you gave some indication of what the number represents you may get a useful answer.