Re: What really is NUMBER under the covers

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: Thu, 03 Feb 2000 00:01:11 -0600
Message-ID: <389919A7.50AEE69B@ntsource.com>

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 Thu Feb 03 2000 - 00:01:11 CST