Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
It's a number. Use a NUMBER. –  Phil Feb 5 '13 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 Feb 5 '13 at 16:52
    
How many rows do you expect? –  dezso Feb 5 '13 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 Feb 5 '13 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 Feb 5 '13 at 17:16

1 Answer 1

up vote 7 down vote accepted

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.

share|improve this answer
    
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 Feb 5 '13 at 17:12
1  
@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 Feb 5 '13 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 Feb 5 '13 at 17:29
1  
@EricDay you have my sympathies then –  JNK Feb 5 '13 at 17:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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