Re: Column space requirements - VARCHAR2 versus NUMBER

> Hello,
>
> Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
>
> I have a table that will contain around 300 million rows so space
> utilization is a primary criterion for the design of the table.
>
> One of the columns will be loaded with a variable length numeric value
> from 1 to 9999999999 that will contain leading zeroes.
>
> Since this numeric column value is not used in any computations, if
> space were not so paramount, I would normally define it as
> varchar2(10) but we are considering defining it as NUMBER (realizing
> that we will lose the leading zeroes when the column is populated).
>
> My understanding is that the NUMBER datatype will generally require
> less space than the VARCHAR2 datatype but is defining the column as
> NUMBER a reasonable design compromise?
>
> TIA
>

While I can understand your concern, I think it may be misplaced.

It is likely that if you have a requirement to have numbers with leading 0, these are not really number, but identifiers (such as a staff number or similar). In this case, you are really dealing with a string of characters that just happen to all be digits.

Now, one of the advantages of varchar2 is that the column will only use the space it requires while number columns will use a fixed size. So, the question is, will the average length of your varchar2 columns be larger than the fixed width number columns? But I don't think that is even the major point and suspect the differences will, in the scale of things, be minimal.

What is possibly more crucial is how the value is going to be processed/used. For example, if the leading zeros are important, maybe you will end up with lots of to_char conversions to pad with leading zeros or maybe you will need to doing joins with other tables where the data is represented as varchar2 etc.

Sorry this isn't really answering the question, but the problem is you cannot give an adequate answer without a lot more information regarding the application and how the value is used. One thing I can say, I've lost count of the number of times developers have used a number column to represent a value when it should have been represented as a varchar2 and was only defined as being a number because it represented some attribute that just happened to have the word number in its name, such as staff number. My rule of thumb is that if the value is not going to be used in mathematical calculations and your in doubt, make it a varchar2.

Tim

-- 
tcross (at) rapttech dot com dot au