Re: Column space requirements - VARCHAR2 versus NUMBER
From: joel garry <joel-garry_at_home.com>
Date: Wed, 4 May 2011 09:35:42 -0700 (PDT)
Message-ID: <1b7e594c-13b9-4547-bf50-d38269fbc2ca_at_i39g2000prd.googlegroups.com>
On May 3, 6: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
Date: Wed, 4 May 2011 09:35:42 -0700 (PDT)
Message-ID: <1b7e594c-13b9-4547-bf50-d38269fbc2ca_at_i39g2000prd.googlegroups.com>
On May 3, 6: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
OK, so you are going to have at most 10 digits. From http://www.jlcomp.demon.co.uk/faq/num_size.html that would take up 7 bytes (length of data plus column overhead) v. 11. So that would be 2.1G versus 3.3G.
I think you should put more weight on the processing requirements and the issues Mark pointed out, and less on 1G here or there. If you are doing full table scans, you might have other things to worry about in your design.
You definitely need to evaluate whether the leading zero requirement is just a holdover from the past or an over-generalized design, and should be just a display issue.
jg
-- _at_home.com is bogus. http://news.techworld.com/applications/3277333/oracle-doing-a-good-job-with-mysql-code-says-former-ceo/Received on Wed May 04 2011 - 11:35:42 CDT