Oracle Table Design, Storing Small Values

Question

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?

• Philᵀᴹ: It's a number. Use a NUMBER.
• Eric Day: @Phil From my understanding of the Oracle documention link a NUMBER(1) is stored as two BYTEs verus CHAR(1) which is stored as one BYTE.
• András Váczi: How many rows do you expect?
• Eric Day: @dezso Only in the 10's of millions every three months. Though there are roughly 70 columns that have this type of data.
• miracle173: how much percent of space will you save in your database by defining these columns as CHAR(1) instead of NUMBER(1)?

Answer 1

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 in n bytes.

I'd always store numbers in a NUMBER. Unless you're dealing with trillions of rows, the space saving will not be significant enough.

• Eric Day: Is there an advantage as stroing it as a number? I agree about storing numbers as numbers, but there are not really numbers. That is to say, the numbers are really just codes for other values, like Male/Female, Yes/No, etc. So though it's a numeric representation it's not really a number, it's more an alias.
• JNK: @EricDay but it's still a number representing a foreign key to another table that uses a number as a primary key. And putting numeric data in string fields is a pretty terrible idea for a variety of reasons, not least of which are lack of data integrity, increased space on disk, and funky sorting
• Eric Day: @JNK The irony is that there are no forign key's in this case. This is just how the buiness data is stored.
• JNK: @EricDay you have my sympathies then