Re: Column space requirements - VARCHAR2 versus NUMBER

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Wed, 4 May 2011 07:05:04 -0700 (PDT)
Message-ID: <011cbac6-f9c0-48eb-9ec6-eea6e1ad0a9f_at_d1g2000yqm.googlegroups.com>



On May 3, 9:26 pm, Luke <luke_ai..._at_hotmail.com> wrote:
> 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
>
> Luke

If the column will always contain only digits not just now but also in the future then yes using data type number instead of varchar2 may make sense however you also need to consider if this value is stored in any other tables and if so if the tables will ever be joined on this column. If the tables will be joined on this column then how the column is defined in these other table matters since you really do not want data type conversion to take place on the join. Good design would dictate that the same data in more than one table should be defined using the same column name, data type, and associated data type specification (length, number of significant digits, etc ,,,) in all tables.

When the column also appears in other tables you would want to check the other tables data to be sure the values are always digits in these tables also. The presence of non-digits in these other tables would mean not only would data type conversion be required on joins but the direction of the conversion becomes important.

You could easily use the to_char(column,'00000009') function with the appropriate mask to display the leading zeroes on retieval. The conversion to number would discard them without issue.

HTH -- Mark D Powell -- Received on Wed May 04 2011 - 09:05:04 CDT

Original text of this message