## Size of NUMBER

Sometimes curiosity gets me into researching something that may not be of use at times, or maybe I just don’t have the wisdom to realise its value.

The last few weeks, whilst I was researching into a problem at work, I found myself sidetracked into learning the size of NUMBERs stored in an Oracle database, and more specifically, how Oracle stores NUMBERs internally. The size of NUMBERs in Oracle database is not a fixed number like Java’s int – 4 bytes, it is variable depending on the value.

Before the story continues, this information is documented in the Concept Guide: Oracle10g and Oracle9i. Oracle may change its implementation with every release, therefore, it is best to check the documentation. On top of that, some Metalink Notes also goes into demonstrating how the internal representation works: 1007641.6 – Understanding Oracle NUMBER Datatype; 1031902.6 – How Does Oracle Store Internal Numeric Data. For more reading, Chapter 12 of Thomas Kyte’s Expert Oracle Database Architecture book provides more explanation.

A numeric column can be defined as NUMBER or NUMBER (p, s), where p = precision (total number of digits) and scale (number of digits to the right of the decimal point). “*Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. Oracle does not store leading and trailing zeroes.*” – Note1031902.6.

For example, if I declare a NUMBER(5,2) column, this means I can store values up to precision (p) = 5 and scale (s) =2. Since values are represented in scientific notation, the number 123.45 has p=5 and s=2. It is interesting to note the number 10000 will not fit in a NUMBER(5,2) column because 10000.00 has 7 digits in total when converted to scale of 2 – a similar example is explained in Tom’s book.

Interesting thing is, how the column is defined in the table does not have a direct relation with the size (bytes) of the NUMBER it stores. To demonstrate, I will create a table with a NUMBER(5,2) column and insert a few values, then use the dump() and vsize() to show the internal representation:

SQL> create table number_test (i number(5,2)); Table created. SQL> insert into number_test (i) values (123.45); 1 row created. SQL> insert into number_test (i) values (12.23); 1 row created. SQL> insert into number_test (i) values (1); 1 row created. SQL> insert into number_test (i) values (0.99); 1 row created. SQL> insert into number_test (i) values (1234.12); insert into number_test (i) values (1234.12) * ERROR at line 1: ORA-01438: value larger than specified precision allows for this column SQL> insert into number_test (i) values (100); 1 row created. SQL> commit; Commit complete. SQL> col dump format a40 SQL> select i, dump(i) as dump, vsize(i) as vsize from number_test; I DUMP VSIZE(I) ---------- ------------------------------ ---------- 123.45 Typ=2 Len=4: 194,2,24,46 4 12.23 Typ=2 Len=3: 193,13,24 3 1 Typ=2 Len=2: 193,2 2 .99 Typ=2 Len=2: 192,100 2 100 Typ=2 Len=2: 194,2 2

Interesting results to observe from the above output, the dump() function showed the length of NUMBER values, as well as how the NUMBER is internally represented. For example, 123.45 is represented in 4 bytes and the value of each byte is 194, 2, 24, 46 respectively. How these four bytes represent the actual value is explained in Note 1007641.6.

Perhaps what us mere mortals are really after, is a simple formula that can calculate the size of the NUMBER values. This is given in the Oracle Concept Guide and Metalink Note in a slightly different form:

Oracle Concept Guide: ROUND((length(p)+s)/2))+1, where p = precision of the value, s = 0 if value is positive; s = 1 if value is negative.

Metalink Note 1031902.6: FLOOR[(p+1)/2] + 1 and add +1 byte (only for negative numbers where the number of significant digits is less than 38), where p = prevision of the value and scale has no effect.

The mind-boggling thing is these two formulas are actually the same, with a little interpretation required: length(p) actually means the number of significant digits without leading or trailing zeroes, and the p in the Metalink Note formula actually meant length(p). So following my examples above:

Number |
length(p) |
ROUND((length(p)+s)/2)+1 |
FLOOR((p+1)/2)+1 |

123.45 | 5 | ROUND(5/2)+1 = 4 | FLOOR((5+1)/2)+1 = 4 |

12.23 | 4 | ROUND(4/2)+1 = 3 | FLOOR((4+1)/2)+1 = 3 |

1 | 1 | ROUND(1/2)+1 = 2 | FLOOR((1+1)/2)+1 = 2 |

0.99 | 2 | ROUND(2/2)+1 = 2 | FLOOR((2+1)/2)+1 = 2 |

100 | 1 | ROUND(1/2)+1 = 2 | FLOOR((1+1)/2)+1 = 2 |

It is not hard to prove mathematically that ROUND(x/2)+1 = FLOOR((x+1)/2)+1, where x >= 0.

[…] Original post by Frederick Tang Weblog […]

Books and Magazines Blog » Archive » Size of NUMBERJuly 10, 2008 at 8:57 am

I’ve been searching the size of number in oracle for long.

This help me so much, thanks for your sharing!

But i try this number 799.008

length(p) = 6

round(6/2) + 1 = 4

but when i try this query select vsize(799.008) from dual;

it returns 5 ???

Pls help,

Thanks

BethDecember 21, 2008 at 8:52 am

Thanks Beth,

Sorry to get back to you this late, I have been on Christmas and New Year holidays from work.

It is a very good question. It took me a while to research a little further, and I am glad to delve into it once more. I am by no means the authorative answer to this, so perhaps consult official sources if my explanation still seems doubtful. Others who knows better are welcome to contribute or correct me here.

What I found is, when the manual says “Oracle does not store leading and trailing zeroes”, that may not be true for the digits after the decimal point. Let’s see how this works (by working backwards) using your example:

SQL> select dump(799.008) as dump, vsize(799.008) as vsize from dual;

DUMP VSIZE

—————————— ———-

Typ=2 Len=5: 194,8,100,1,81 5

Indeed, 5 bytes are used to store 799.81 internally in my Oracle9i (9.2.0.8) database. If I apply the methdology explained in Note 1007641.6…

The 1st byte is the sign/exponent byte:

1st byte – 194 (binary: 11000010) tells me it’s a positive number; 1s complement of the binary number is 01000010, equals 66 in decimal. Base 64 of 66 is 2. This means the exponent is 2.

The following data bytes are in base 100, and value of 1 is added to avoid a binary zero.

2nd byte – 8 tells me it’s a positive number, deduct 1 from 8 equals 7.

3rd byte – 100 tells me it’s a positive number, deduct 1 from 100 equals 99.

4th byte – 1 tells me it’s a positive number, deduct 1 from 1 equals 0.

5th byte – 81 tells me it’s a positive number, deduct 1 from 81 equals 80.

so… the number 799.008 = 7×100^1 + 99×100^0 + 0x100^-1 + 80×100^-2

This tells me that the number is actually stored as 799.0080, because of the base 100 calculation. Therefore, length(p) actually equals 7. Plug this number back into the formulas:

ROUND[(7+0)/2] + 1 = 5

FLOOR[(7+1)/2] + 1 = 5

This is the only explanation I can come up with, and hope this helps…

fredericktangJanuary 5, 2009 at 1:13 am

hey ……thanxs a lot this site has really helped me out with vsize

PoojaNovember 24, 2009 at 7:13 am

[…] Size of NUMBER Frederick Tang Weblog […]

How to calculate the actual size of a table? | WandererNovember 8, 2012 at 3:41 pm