Re: Oracle: why should one specify the width of a number column?

On Oct 10, 6:26 am, astaroth <kmehk..._at_gmail.com> wrote:
> VTR250 wrote:
> > 100 takes up less space than 9999  --  so it is correct: you really do
> > save 1 byte.  If I'm reading your post wrongly, what specifically is
> > incorrect in the original statement?   This is a digression, however,
> > because we're all in agreement that you don't need to save the byte, but
> > the size limit is a good idea because it helps stop junk data getting
> > in.
>
> Two things:
> 100 takes up less space than 9999 - that's right, because 100 has less
> significant digits.
>
> And what I did disagree with:
>
> >> > Even though I know a NUMBER(4) for a year, or a NUMBER(3) for a
> >> > person's age actually takes less bytes of storage than NUMBER,
>
> A NUMBER(4) for a year will take exactly the same amount of bytes of
> storage than NUMBER! Precision and scale are only constraints that
> prevent some values from being stored, but do not affect how much
> space you need to store it.
>
> 9999 takes 3 bytes whether stored in NUMBER, NUMBER(4), NUMBER(10) or
> NUMBER(12,2) - but Oracle will just reject this value for NUMBER(3)
> column.
>
> --
> astaroth

I understand: an number x takes requires the same number of bytes to store in a column regardless of whether the column has been defined with a size.

     create table t ( x number, y number(4) );
     insert into t values (999, 999);
     select x, y, vsize(x), vsize(y) from t;
              X          Y   VSIZE(X)   VSIZE(Y)
     ---------- ---------- ---------- ----------
            999        999          3          3

So my original comment "I know a NUMBER(4) for a year, or a NUMBER(3) for a person's age actually takes less bytes of storage than NUMBER" is wrong.

Thanks for the correction. Received on Mon Oct 12 2009 - 23:41:06 CEST