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:
|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.