2

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?

Improve this question
7
  • 2
    It's a number. Use a NUMBER.
    – Philᵀᴹ
    Commented Feb 5, 2013 at 16:45
  • @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.
    – Eric Day
    Commented Feb 5, 2013 at 16:52
  • How many rows do you expect?
    – András Váczi
    Commented Feb 5, 2013 at 16:55
  • @dezso Only in the 10's of millions every three months. Though there are roughly 70 columns that have this type of data.
    – Eric Day
    Commented Feb 5, 2013 at 17:06
  • 1
    how much percent of space will you save in your database by defining these columns as CHAR(1) instead of NUMBER(1)?
    – miracle173
    Commented Feb 5, 2013 at 17:16

1 Answer 1

Reset to default
8

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.

Improve this answer
4
  • 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.
    – Eric Day
    Commented Feb 5, 2013 at 17:12
  • 2
    @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
    – JNK
    Commented Feb 5, 2013 at 17:18
  • @JNK The irony is that there are no forign key's in this case. This is just how the buiness data is stored.
    – Eric Day
    Commented Feb 5, 2013 at 17:29
  • 1
    @EricDay you have my sympathies then
    – JNK
    Commented Feb 5, 2013 at 17:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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