In Oracle if you want to store a small value, such as a one digit code (e.g. 0, 1, 2, 3 4, or Null; or Null, or 1; or 1, or 2), what is the space optimal way to store such a value. Currently I'm looking at a system where it was decided to store these values as VARCHAR2(11 CHAR), but it seems like the wrong option for a column type. From the online Oracle documentation and the almighty Google it seems like the more space optimal storage option with be CHAR(1) as it seems to be the smallest on disk type.
Am I completely wrong in my thinking or doesn’t it not matter either way?
Best Answer
The documentation states the size of datatypes here.
0 (the number) is stored as 1 byte. Other single digit numbers will be stored in 2 bytes (one for the exponent, one for the mantissa). You can test this yourself by creating a test table and using the
VSIZE()function on test data (doc link).A
CHAR(n)will be stored innbytes.I'd always store numbers in a
NUMBER. Unless you're dealing with trillions of rows, the space saving will not be significant enough.