Re: Column space requirements - VARCHAR2 versus NUMBER
Date: Sat, 7 May 2011 11:56:46 +0200
Message-ID: <slrnisa5qu.dik.hjp-usenet2_at_hrunkner.hjp.at>
On 2011-05-04 01:26, Luke <luke_airig_at_hotmail.com> wrote:
> 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.
What does "variable length ... that will contain leading zeroes" mean exactly:
- The value without leading zeroes is variable length, leading zeroes are used to pad to 10 digits ("0000000042" is a legal value, but "00042" is not).
- The length including leading zeroes is variable, but the leading zeroes are not significant ("0000000042" and "00042" could both occur but are considered the same value)
- The length including leading zeroes is variable and all digits are significant ("0000000042" and "00042" can both occur and are different values).
> 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).
This will only really work in the case 1 above. It will sort of work in case 2 (i.e. you can still identify your records but users may be irritated if "the computer eats their zeroes").
It will not work at all in case 3. You can, however, make it work in case 3 by prepending a fixed digit. So you store "0000000042" as 10000000042 and "00042" as 100042. For display purposes you strip the first digit. This will add 0.5 bytes per row on average. Alternatively you could use a varchar2 and store each pair of digits in a single ASCII character. That would shave off another byte compared to storing it as number, but has the disadvantage that you have cryptic strings instead of readable numbers in your database - probably not worth the bother.
hp Received on Sat May 07 2011 - 04:56:46 CDT