Re: Column space requirements - VARCHAR2 versus NUMBER
From: Luuk <Luuk_at_invalid.lan>
Date: Tue, 14 Jun 2011 20:29:17 +0200
Message-ID: <4df7a87a$0$49047$e4fe514c_at_news.xs4all.nl>
On 14-06-2011 10:44, Tim X wrote:
> Luke <luke_airig_at_hotmail.com> writes:
>
>
> 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
>
>
>
>
Date: Tue, 14 Jun 2011 20:29:17 +0200
Message-ID: <4df7a87a$0$49047$e4fe514c_at_news.xs4all.nl>
On 14-06-2011 10:44, Tim X wrote:
> Luke <luke_airig_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));
?
-- LuukReceived on Tue Jun 14 2011 - 13:29:17 CDT