How much space does a number take up.

Author's name: Jonathan Lewis

Author's
Email: Jonathan@jlcomp.demon.co.uk

Date written: 26th July 2001

Oracle
version(s): 7.3 - 8.1.7.0

It is possible to define a number so that
it is stored in the database with 38 digits of precision.
If I do this, how much space does a number really use if
it doesn't actually use this degree of precision.
Strangely, the internal storage for number
(dba_tab_columns) suggests that the worst case usage for
a number is 22 bytes, so how much space does it really
take to store a number ?.

Oracle uses an internal 'base 100 encoding' format for storing
numbers, which stores two digits of precision per byte, an extra
byte that holds both the sign and the 'mantissa' indicating where
to put the decimal point in relation to the digits of precision,
and for negative numbers a terminating byte holding the value
0x66.

Consequently the actual space used by a number depends on the
number of significant digits that appear in the number and the
sign of the number, so the numbers 1, 100, 10,000 and 1,000,000
all take two bytes, and -1, -100, -10,000, -1,000,000 will take 3
bytes , whereas 1,234,567 will require 5 bytes, and -1,234,567
will require 6 bytes.

Value

Representation

Bytes Stored

1

1 x power(100,0)

c1, 2

100

1 x power(100,1)

c2, 2

10,000

1 x power(100,2)

c3, 2

1,000,000

1 x power(100,3)

c4, 2

-1

-1 x power(100,0)

3e, 64, 66

-100

-1 x power(100,1)

3d, 64, 66

-10,000

-1 x power(100,2)

3c, 64, 66

-1,000,000

-1 x power(100,3)

3b, 64, 66

1,234,567

1.234,567 x power(100,3)

c4, 2, 18, 2e, 44

-1,234,567

-1.234,567 x power(100,3)

3b, 64, 4e, 38, 22, 66

Although there are special cases, as indicated by the values
for 1, 100, 10,000, and 1,000,000 above, the rules tell us that
the typical N-digit positive number will have require 1 +
ceil(N/2) bytes, and a negative number will required 2 +
ceil(N/2) bytes, where ceil() is found by rounding up to the next
integer where necessary.

For example, most of the (six digit) numbers from 100,000 to
199,999 will encode to 4 bytes i.e.1 + (6/2), whereas most of the
(nine digit) numbers from 100,000,000 to 100,099,999 will encode
to 6 bytes i.e.1 + ceil(9/2) = 1 + 5. You can run the following
SQL (as SYS because of the reference to the X$ object) to verify
the second result: