Frederick Tang Weblog

Stories about Oracle concepts I have learnt from work, and the occasional brain-dump…

Size of NUMBER

with 5 comments


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.

About these ads

Written by fredericktang

July 10, 2008 at 7:53 am

Posted in 10g, 9i, Oracle

5 Responses

Subscribe to comments with RSS.

  1. [...] Original post by Frederick Tang Weblog [...]

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

    Beth

    December 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 + 0×100^-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…

      fredericktang

      January 5, 2009 at 1:13 am

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

    Pooja

    November 24, 2009 at 7:13 am

  4. [...] Size of NUMBER Frederick Tang Weblog [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: