Re: What really is NUMBER under the covers

Because numbers are stored with varying length, the original poster should try

SQL*Plus: Release 8.1.5.0.0 - Production on Mo Feb 7 14:50:54 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production

SQL> select vsize (99999999.99) from dual;

VSIZE(99999999.99)

                 6

SQL> select vsize (9999999999.99) from dual;

VSIZE(9999999999.99)

                   7

SQL> select vsize (-9999999999.99) from dual;

VSIZE(-9999999999.99)

                    8

SQL> select vsize (-999999999.99) from dual;

VSIZE(-999999999.99)

                   8

SQL> As Thomas Kyte once pointed out the space needed does not depend on the definition but on the real values stored.

Martin

Frank Hubeny wrote:
>
> One way to see how much space is being used is to
> use vsize.
>
> For example:
>
> SQL> select vsize(1) from dual;
>
> VSIZE(1)
> ---------
> 2
>
> I don't think the constraint (10,2) affects the storage of the number.
> It only limits the possible values that could be stored in the field.
>
> Using vsize you can easily construct a test table and check the sizes of
> fields with number(10,2) and number datatypes. I think you will find
> that they consume the same amount of storage if they are small enough to
> load into the number(10,2) field. Also they consume a variable amount
> of space depending upon the size of the number.
>
> The dump function is also interesting:
>
> Example:
>
> SQL> select dump(1) from dual;
>
> DUMP(1)
> ------------------
> Typ=2 Len=2: 193,2
>
> I have never been able to figure out how or why the number 1 gets
> represented internally as 193,2. And I wonder how arithmetic is done
> with numbers in this representation.
>
> The documentation for vsize and dump are in A67779.pdf (SQL Reference,
> 8.1.5), pages 175 and 134.
>
> There is also a page devoted to "Internal Numeric Format" in A67781.pdf
> (Oracle 8i Concepts) page 369. However, the explanation here and what
> one gets from the dump function leave me puzzled.
>
> I hope I answered your initial question about storage and I hope someone
> can clarify my continuing questions about the internal representation of
> the number datatype.
>
> Frank Hubeny
>
> Ken Sproule wrote:
>
> > I would like to thank everyone in advance for your help.
> >
> > My question is simply that I need to know exactaly what Oracle does
> > with a NUMBER(10,2) internally. I mean, How much space is actually
> > consumed on the disk in storage AND where is this documented.
> >
> > Does NUMBER work like VARCHAR/2 in that NUMBER without the ( 10, 2)
> > would be big enough to hold a 40 character numeric ( that's what a
> > double), and ( 10 , 2 ) is a long?
> >
> > Best,
> >
> > Ken Sproule
> > Kenmn_at_tds.net
Received on Mon Feb 07 2000 - 06:58:04 CST