DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Number Data Type

  1. #1
    Join Date
    Jul 2000
    Location
    Atlanta
    Posts
    15
    Hi,

    I am trying to figure out how big of a number can you store in Number datatype. I know that the precision can be from 1 to 38. Also I would like to know/understand what the size in Bytes would it be in the following cases.

    1) number
    2) number(2)
    3) number(4)
    4) number(6) ... and so on


    Thanks for all the help.


  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Mar 2001
    Posts
    314
    To find the actual number of bytes occupied by a field value use the VSIZE function.

    -amar

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Examples:

    7 needs 2 bytes: 1 for the digit and 1 for exponent
    -7 needs 3 bytes: 1 for the digit, 1 for exponent, 1 for the sign
    777 needs 3 bytes: 2 for digits and 1 for the exponent
    7700 needs 2 bytes (why :-))


  5. #5
    Join Date
    Jul 2000
    Location
    Atlanta
    Posts
    15
    Thank you all very much for the help. The below is what I was looking for which I found from this link: http://oradoc.photo.net/ora816/serve...0datyp.htm#743


    The column size in bytes for a particular numeric data value NUMBER (p), where p is the precision of a given value, can be calculated using the following formula:

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



  6. #6
    Join Date
    Mar 2001
    Posts
    314
    I would stick to VSIZE while trying to find out the number of bytes internally occupied by oracle to store a certain value. I don't know how the formula works ( length(p) = what? ) - tried to figure out though. Take a look at the following:

    value----------bytes needed using vsize
    ------- -----------------------------

    1 -------------> 2
    10 ------------> 2
    100 -----------> 2
    1000 ----------> 2
    10000 ---------> 2
    100000 --------> 2
    100001 --------> 4
    876943 ---------> 4
    -1 ---------------> 3


    The reason is :

    Oracle stores a number in base 100 format
    Each byte can store 2 digits
    One byte is always reserved for exponent
    For -ve numbers an additional byte is reqd (for sign)

    Thus, for Julian's number:

    7700 needs 2 bytes because:
    the digits 77 need one byte
    the exponent 00 needs one byte
    thus 7700 needs 2 bytes

    This holds true for all numbers - as long as there are trailing zeroes, the space requirement does not increase.

    I would be interested in an explanation of the formula if anyone has any please If oracle has it in the doc's there would be a logic behind it I think!

    -amar

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by amar
    I would be interested in an explanation of the formula if anyone has any please If oracle has it in the doc's there would be a logic behind it I think!
    The formula is explained in the Concepts manual (just follow the URL spatemp has provided, just clik on it ). p represents precision, s represents scale.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by amar
    I would stick to VSIZE while trying to find out the number of bytes internally occupied by oracle to store a certain value.
    This reminded me of another one from the world of Oracle puzzles.

    It is well known fact that dates occupy 7 bytes of storage in Oracle RDBMS - one byte for each of the following date elements: century, year, month, day, hour, minute, and second.

    So how come VSIZE() returns the following?

    SQL> select vsize(sysdate) from dual;

    VSIZE(SYSDATE)
    --------------
    8

    So, is it 7 or 8 bytes for dates? Are the manuals wrong (no, they are not!)? Does VSIZE() sometimes return incorect value (no, it does not!)? Any takers to explain this?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    So how come VSIZE() returns the following?

    SQL> select vsize(sysdate) from dual;

    VSIZE(SYSDATE)
    --------------
    8

    SYSDATE is "other" type, not date, not number. The value of SYSDATE is derived by interrogating the system clock.


  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, I still think SYSDATE is a perfect example of DATE datatype. Scalar datatypes are defined in very exact way in Oracle, there is no such type as "other" type . Anyway, of what datatype is the following expression?

    to_date('01.01.2001','dd.mm.yyyy')

    Another perfect example of DATE datatype, I would say. And it has nothing to do with system clock this time. So how come VSIZE() again returns 8 for it?

    SQL> select vsize(to_date('01.01.2001','dd.mm.yyyy')) from dual;

    VSIZE(TO_DATE('01.01.2001','DD.MM.YYYY'))
    -----------------------------------------
    8
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width