Simple question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Simple question

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Unhappy

    Hi all,

    What is the default column size for data type NUMBER.

    Eg. When we say DATE, column length is 7 bytes. Similarly, in case of NUMBER, how much size is reserved.

    I did not find much documentation on this.

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    http://technet.oracle.com/docs/produ...typ.htm#427846

    On a 32bit OS its 8byts I presume.

    Good discussion:
    http://dbasupport.com/forums/showthr...threadid=17375

    Sam

    [Edited by sambavan on 02-25-2002 at 06:06 PM]
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    There is no default column size for column of type NUMBER, i.e. no space is reserved in database for nonexisting values (NULLs) of type NUMBER.

    When you store some values in such column, the physical space used by those values depends on the actual values stored. It can vary anywhere from 1 byte to 21 bytes.

    You can check the size used by values stored in your table by isuing:

    SELECT col1, VSIZE(col1) bytes_used FROM my_table;

    You can find out how much space your number will use if stored in the table by isuing the following (example for number -123.4567):

    SELECT VSIZE(-123.4567) FROM dual;

    You can calculate the space used by numeric data by using formula in the following URL:
    http://technet.oracle.com/docs/produ...0datyp.htm#743
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    While doing table sizing, if the table designer does not give any length and just gives Col1 NUMBER, how much bytes should I take while doing new table sizing.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi guys,

    Any update plz...

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  6. #6
    Join Date
    Apr 2001
    Posts
    107
    Look at Jurij's answer, I thing everything is there...


  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    By default 22 bytes are reserved for the column whose type is NUMBER.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by tamilselvan
    By default 22 bytes are reserved for the column whose type is NUMBER.
    By default,0 bytes are reserved for the column, no matter of what type it is!
    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
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    From the docs:
    Oracle stores numeric data in variable-length format. Each value is stored in scientific notation, with one byte used to store the exponent and up to 20 bytes to store the mantissa. The resulting value is limited to 38 digits of precision. Oracle does not store leading and trailing zeros. For example, the number 412 is stored in a format similar to 4.12 x 102, with one byte used to store the exponent (2) and two bytes used to store the three significant digits of the mantissa (4, 1, 2). Negative numbers include the sign in their length.

    Taking this into account, 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


    where s equals zero if the number is positive and s equals 1 if the number is negative.

    Zero and positive and negative infinity (only generated on import from Version 5 Oracle databases) are stored using unique representations. Zero and negative infinity each require one byte; positive infinity requires two bytes
    I am still not clear if anything is reserved for NULLS??

    Sanjay

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    [QUOTE]Originally posted by SANJAY_G
    I am still not clear if anything is reserved for NULLS??
    Nothing is reserved for NULL value if this column is the last one in a table or if all other trailing columns contain only NULL values. If any column following this NULL value contains any non-NULL value then 1 byte is reserved for this NULL value column.
    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