Re: Column space requirements - VARCHAR2 versus NUMBER
From: joel garry <joel-garry_at_home.com>
Date: Wed, 15 Jun 2011 11:27:14 -0700 (PDT)
Message-ID: <468ea900-3df0-461e-9c00-1353be385363_at_l2g2000prg.googlegroups.com>
On Jun 14, 11:29 am, Luuk <L..._at_invalid.lan> wrote:
> On 14-06-2011 10:44, Tim X wrote:
>
>
>
> > Luke <luke_ai..._at_hotmail.com> writes:
>
> >> 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
>
> If those columns always contain 10 numbers
> would it make a difference to define the table as
> CREATE TABLE table ( column1 CHAR(20));
> or
> CREATE TABLE table ( column1 VARCHAR(20));
>
> ?
>
> --
> Luuk
Date: Wed, 15 Jun 2011 11:27:14 -0700 (PDT)
Message-ID: <468ea900-3df0-461e-9c00-1353be385363_at_l2g2000prg.googlegroups.com>
On Jun 14, 11:29 am, Luuk <L..._at_invalid.lan> wrote:
> On 14-06-2011 10:44, Tim X wrote:
>
>
>
> > Luke <luke_ai..._at_hotmail.com> writes:
>
> >> 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
>
> If those columns always contain 10 numbers
> would it make a difference to define the table as
> CREATE TABLE table ( column1 CHAR(20));
> or
> CREATE TABLE table ( column1 VARCHAR(20));
>
> ?
>
> --
> Luuk
Canonical answer: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:123212348063
jg
-- _at_home.com is bogus. http://www.informationweek.com/news/software/bi/230700013Received on Wed Jun 15 2011 - 13:27:14 CDT