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

On Oct 3, 4:56 am, astaroth <astarot..._at_o2.pl> wrote:
> VTR250 wrote:
> > 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,
>
> That's actually incorrect. Oracle stores numbers in variable length
> format. Specified size is only a column constraint (which may or may not
> be a good choice).
>
> http://www.jlcomp.demon.co.uk/faq/num_size.html
>
> --
> astaroth

er... the way I'm reading your post, since you say NUMBER type is in fact a varying length data type on disk you must be picking at 3 digits and 4 digits requiring the same amount of space (by number(3) or number(4) I mean a three or four-digit number respectively stored in a table column).

The following information appears in the page you linked to, but it's clearer on the Ask Tom page (http://asktom.oracle.com/pls/asktom/f? p=100:11:0::::P11_QUESTION_ID:1856720300346322149)

<snip>

         X Y VSIZE(X) VSIZE(Y) ---------- ---------- ---------- ----------

        99        100          2          2
      9999      10000          3          2

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.

Thanks. Received on Mon Oct 05 2009 - 00:10:46 CEST