Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Balasubramanian.

Asked: November 05, 2001 - 9:44 pm UTC

Last updated: July 10, 2006 - 9:00 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I find the length of a 99 digit number in two ways.
Case 1 : I find the length of this number without converting to char.
Case 2: I find the length of this number with converting to char.

But both gives different result. But for small digit numbers it gives the same result. Can you explain whis happens ?

SQL> select length(171122452428141311372468338881272839091000000000000000000000000000000000000000000000000000000000000) case1
2 from dual
3 /

CASE1
----------
40

SQL> select length('171122452428141311372468338881272839091000000000000000000000000000000000000000000000000000000000000') case2
2 from dual
3 /

CASE2
----------
99


Thanks,
Bala


and Tom said...

Sure, you are doing an IMPLICIT conversion from NUMBER to STRING in case 1. You gave a number (and numbers in Oracle have 38 digits of precision). That is a valid number but the DEFAULT string representation of that is:

1* select to_char( 171122452428141311372468338881272839091000000000000000000000000000000000000000000000000000000000000 ) from dual
ops$tkyte@ORA717DEV.US.ORACLE.COM> /

TO_CHAR(17112245242814131137246833888127
----------------------------------------
1.7112245242814131137246833888127284E+98

ops$tkyte@ORA717DEV.US.ORACLE.COM>


So, you got the length of that NUMBER when depicted as a string.

In the second case, you never had a number -- you had a STRING that could have contained "how now brown cow" just as easily as the ASCII characters that represent digits you used. You measured the length of a string.

You compared an apple (the number) to a toaster oven (the string). There is no way to compare these two as they are wholly different.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

That was a Good one.

Ganesh Raja, November 06, 2001 - 8:43 am UTC

By the way ... How do u calculate the Amount of bytes that a Number Field of Say 10 precision takes internally.

I Wentthru the Concepts guide butit is a bit confusing....


Regards,
Ganesh R

Tom Kyte
November 06, 2001 - 8:58 am UTC

http://docs.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c10datyp.htm#784 

gives the formula:

ROUND((length(p)+s)/2))+1

which is just wrong and doesn't really even make any sense!  Bummer.. Going back to 7.3 I find:


Taking this into account, the column data size for a particular numeric
data value NUMBER (p), where p is the precision of a given value (scale
has no effect), can be calculated using the following formula:

1 byte              (exponent)
FLOOR(p/2)+1 bytes  (mantissa)
+ 1 byte            (only for a negative number where
                     the number of significant digits is
                     less than 38)
====================
number of bytes of data


For example:

ops$tkyte@ORA717DEV.US.ORACLE.COM> create table t ( x number(11) );

Table created.

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> begin
  2          for i in 1 .. 11 loop
  3                  insert into t values ( rpad('9',i,'9') );
  4          end loop;
  5          insert into t values ( 0 );
  6          for i in 1 .. 11 loop
  7                  insert into t values ( '-'|| rpad('9',i,'9') );
  8          end loop;
  9  
 10  end;
 11  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA717DEV.US.ORACLE.COM> 
ops$tkyte@ORA717DEV.US.ORACLE.COM> select x, vsize(x), 1 + floor(10/2) + 1 + 1
  2  from t;

         X   VSIZE(X) 1+FLOOR(10/2)+1+1
---------- ---------- -----------------
         9          2                 8
        99          2                 8
       999          3                 8
      9999          3                 8
     99999          4                 8
    999999          4                 8
   9999999          5                 8
  99999999          5                 8
 999999999          6                 8
9999999999          6                 8
1.0000E+11          7                 8
         0          1                 8
        -9          3                 8
       -99          3                 8
      -999          4                 8
     -9999          4                 8
    -99999          5                 8
   -999999          5                 8
  -9999999          6                 8
 -99999999          6                 8
-999999999          7                 8
-1.000E+10          7                 8
-1.000E+11          8                 8

23 rows selected.

Shows the actual vs max size for each number..

That Wasreal fast ...

Ganesh Rja, November 06, 2001 - 9:31 am UTC

Tom that is okay but still Why do u Say p is 10 and not 11.

what is the logic behind this Calculation.

Regards,
Ganesh R

Tom Kyte
November 06, 2001 - 12:42 pm UTC

my example used 11, use 10 if you would like.

I guess it should be....

Sarada Priya, November 06, 2001 - 11:00 pm UTC

I guess Tom should use floor(11/2) and not floor(10/2) since in the example he has used NUMBER(11). Tom, Please put your feeback.






Tom Kyte
November 07, 2001 - 6:40 am UTC

correct, typo on my part. changes not the answer as floor(10/2) = floor(11/2)

What Was the logic

Ganesh Raja, November 06, 2001 - 11:16 pm UTC

Hi Tom,

What is the Logic behind your calculation. And also comment on priya's statement.

Regards,
Ganesh R

Tom Kyte
November 07, 2001 - 6:40 am UTC

it was a typo, sorry.

please clarify

A reader, June 03, 2004 - 12:46 pm UTC 

Tom,
I tried to use the above formula for calculating the size of the number.

SQL> select vsize(123456), vsize(12345.6), vsize(1234.56), vsize(123.456) from dual

VSIZE(123456) VSIZE(12345.6) VSIZE(1234.56) VSIZE(123.456)
------------- -------------- -------------- --------------
            4              5              4              5


Can you please explain, why the vsize values are different, though the digits are same. I read that the digits after decimal will not have effect on space usage.

Thanks

Tom Kyte
June 03, 2004 - 2:31 pm UTC 

because numbers are stored as varying length strings between 0 and 22 bytes in length.

the more digits you have, the more bytes.

the placement of the decimal point (or lack thereof) affects the alignment and hence the size as well.

ops$tkyte@ORA9IR2> select x, vsize(x) vs, dump(x) dmp from t
  2  /
 
         X         VS DMP
---------- ---------- ----------------------------------------
    123456          4 Typ=2 Len=4: 195,13,35,57
   12345.6          5 Typ=2 Len=5: 195,2,24,46,61
   1234.56          4 Typ=2 Len=4: 194,13,35,57
   123.456          5 Typ=2 Len=5: 194,2,24,46,61

formula ?

A reader, June 03, 2004 - 2:42 pm UTC

Thanks Tom for responding.
So, what formula did the above numbers use ?
Why did 123456 and 1234.56 use 4 bytes and
12345.6 and 123.456 use 5 bytes ?
Can you please explain in terms of the formula used above or how it actually got those sizes. My Math using the formula doesn't serve me right .....

Appreciate your help !!!


Tom Kyte
June 03, 2004 - 3:53 pm UTC 

create table t ( x number, b1 int, b2 int, b3 int, b4 int, b5 int, b6 int );
                                                                                                                                                                                          
                                                                                                                                                                                          
insert into t values ( 123456          , 195,13,35,57, null, null );
insert into t values ( 12345.6          , 195,2,24,46,61, null );
insert into t values ( 1234.56          , 194,13,35,57, null, null );
insert into t values ( 123.456          , 194,2,24,46,61, null );
insert into t values ( -123456 , 60,89,67,45,102, null );
insert into t values ( -12345.6 , 60,100,78,56,41,102 );
insert into t values (-1234.56 , 61,89,67,45,102, null );
insert into t values (-123.456 , 61,100,78,56,41,102 );
column dmp format a35
column b1_bin format a8
column b1_1scomp format a8
column nums format a20
column nums2 format a20
                                                                                                                                                                                          


ops$tkyte@ORA9IR2> select x,
  2         dmp,
  3             exp e,
  4             sign || substr(nums,1,2*exp)||'.'||substr(nums,2*exp+1) nums2
  5    from (
  6  select x,
  7         dmp,
  8             sign,
  9             exp,
 10             b2||b3||b4||b5||b6 nums,
 11             b2, b3, b4, b5, b6
 12    from (
 13  select x,
 14         dmp,
 15             sign,
 16             b1_bin,
 17             b1_1scomp,
 18             case when sign = '+' then bitand(b1,to_dec('01111111',2))-64
 19                  when sign = '-' then to_dec( substr( b1_1scomp, 2), 2 )-64
 20                  end exp,
 21             to_char( decode( sign, '+', b2-1, decode(b2,102,null,100-(b2-1)) ), 'fm00') b2,
 22             to_char( decode( sign, '+', b3-1, decode(b3,102,null,100-(b3-1)) ), 'fm00') b3,
 23             to_char( decode( sign, '+', b4-1, decode(b4,102,null,100-(b4-1)) ), 'fm00') b4,
 24             to_char( decode( sign, '+', b5-1, decode(b5,102,null,100-(b5-1)) ), 'fm00') b5,
 25             to_char( decode( sign, '+', b6-1, decode(b6,102,null,100-(b6-1)) ), 'fm00') b6
 26    from (
 27  select x,
 28         dmp,
 29             decode( bitand(b1,to_dec('10000000',2)), 0, '-', '+' ) sign,
 30             b1, b2, b3, b4, b5, b6, b1_bin, b1_1scomp
 31    from (
 32  select x, dump(x) dmp, b1, b2, b3, b4, b5, b6,
 33         lpad(to_bin(b1),8,'0') b1_bin,
 34             translate(lpad(to_bin(b1),8,'0'),'01','10') b1_1scomp
 35    from t
 36         )
 37         )
 38         )
 39         )
 40  /
 
         X DMP                                  E NUMS2
---------- ----------------------------------- -- --------------------
    123456 Typ=2 Len=4: 195,13,35,57            3 +123456.
   12345.6 Typ=2 Len=5: 195,2,24,46,61          3 +012345.60
   1234.56 Typ=2 Len=4: 194,13,35,57            2 +1234.56
   123.456 Typ=2 Len=5: 194,2,24,46,61          2 +0123.4560
   -123456 Typ=2 Len=5: 60,89,67,45,102         3 -123456.
  -12345.6 Typ=2 Len=6: 60,100,78,56,41,102     3 -012345.60
  -1234.56 Typ=2 Len=5: 61,89,67,45,102         2 -1234.56
  -123.456 Typ=2 Len=6: 61,100,78,56,41,102     2 -0123.4560
 
8 rows selected.


there is the utter destruction/construction of a number :)


In the inner most query we have:

 32  select x, dump(x) dmp, b1, b2, b3, b4, b5, b6,
 33         lpad(to_bin(b1),8,'0') b1_bin,
 34             translate(lpad(to_bin(b1),8,'0'),'01','10') b1_1scomp
 35    from t


that gets the data, all of the bytes -- and byte one in binary and the one's compliment.
http://asktom.oracle.com/~tkyte/hexdec/index.html 


next we figure out the sign:

 27  select x,
 28         dmp,
 29             decode( bitand(b1,to_dec('10000000',2)), 0, '-', '+' ) sign,
 30             b1, b2, b3, b4, b5, b6, b1_bin, b1_1scomp


if the high bit of byte 1 is on -- postive, else negative....

then we:

 13  select x,
 14         dmp,
 15             sign,
 16             b1_bin,
 17             b1_1scomp,
 18             case when sign = '+' then bitand(b1,to_dec('01111111',2))-64
 19                  when sign = '-' then to_dec( substr( b1_1scomp, 2), 2 )-64
 20                  end exp,
 21             to_char( decode( sign, '+', b2-1, decode(b2,102,null,100-(b2-1)) ), 'fm00') b2,
 22             to_char( decode( sign, '+', b3-1, decode(b3,102,null,100-(b3-1)) ), 'fm00') b3,
 23             to_char( decode( sign, '+', b4-1, decode(b4,102,null,100-(b4-1)) ), 'fm00') b4,
 24             to_char( decode( sign, '+', b5-1, decode(b5,102,null,100-(b5-1)) ), 'fm00') b5,
 25             to_char( decode( sign, '+', b6-1, decode(b6,102,null,100-(b6-1)) ), 'fm00') b6


that uses the sign to interpret the first byte.  If positive, the exponent is going to be the last 7 bits minus 64, else it is the last 7 bits of the ones compliment minus 64.

Also, we pad out each byte to be a 2 character "number".  If positive, we subtract 1, if negative -- and not 102 (flag byte), then it is 100 MINUS the byte minus 1...

Then we:

  6  select x,
  7         dmp,
  8             sign,
  9             exp,
 10             b2||b3||b4||b5||b6 nums,

put the number back together... and lastly:

ops$tkyte@ORA9IR2> select x,
  2         dmp,
  3             exp e,
  4             sign || substr(nums,1,2*exp)||'.'||substr(nums,2*exp+1) nums2

stick the exponent back in.  QED.

Size of NULL columns

Deepak, July 10, 2006 - 7:08 am UTC

Hi Tom,

Did the following experiment:

create table len_test(
c1 number,
c2 char,
c3 varchar2(1));

insert into len_test values(null,null,null);

select vsize(c1) vsc1,dump(c1) dmpc1,vsize(c2) vsc2,dump(c2) dmpc2,vsize(c3) vsc3,dump(c3) dmpc3 from len_test;

VSC1 DMPC1 VSC2 DMPC2 VSC3 DMPC3
----- ----- ----- ----- ---- -----
NULL NULL NULL

The output says that the char column did not consume any space. But as per Oracle docs. the char column occupies minimum of 1-byte.

Can you please help me in getting the concept right?


Tom Kyte
July 10, 2006 - 8:04 am UTC

trailing NULL columns consume zero bytes - they are not physically stored.

if you

insert into len_test values ( 1, '1', null );

then C3 takes zero bytes.


If you

insert into len_test values ( 1, null, null );

both c2 and c3 consume zero bytes. However if you:

insert into len_test values (1, null, '1' );

now c2 will consume space - to indicate "I am here, but I am null"


"trailing null columns are not stored", that is the bottom line.

review

Deepak, July 10, 2006 - 8:31 am UTC

Hi Tom,

Thanks for your ever valuable feedback.

But I still Have a doubt. I did the following:

insert into len_test values(1,null,'A');

select vsize(c1),dump(c1),vsize(c2),dump(c2),vsize(c3),dump(c3) from len_test;

VSC1 DMPC1 VSC2 DMPC2 VSC3 DMPC3
----- ----- ---- ---- ----- ----
2 Typ=2 Len=2: 193,2 NULL 1 Typ=1 Len=1: 65


You have said in your answer that if it is a non-trailing column then it will have a space. As space is also a character the vsize should return 1 for c2, which it did not in the above case.

Am I missing anything Tom. Please help...



Tom Kyte
July 10, 2006 - 9:00 am UTC

vsize returns NOT the amount of space taken by the entire thing on disk. vsize returns the size of the thing - you have a varchar2(1) with a single character. That takes 1 byte (plus the length byte).


and here, c2 is NULL so vsize says "I don't know, f(null) is null by definition"

you would have to dump blocks (no, I'm not going to, waste of time for this) to see this.

Thanks a lot Tom

Deepak, July 10, 2006 - 9:03 am UTC

Thanks a lot Tom for your nice explanation...

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.