Re: NUMBER Storage

A copy of this was sent to Norris <johnnie_at_cooper.com.hk> (if that email address didn't require changing) On 17 Sep 1999 08:21:05 GMT, you wrote:

>What is the minimum and maximum byte storage for NUMBER?
>

If you read below - you find facts such as:

... The resulting value is limited to 38 digits of precision... ... a formula to plug P into

So,

1 byte + floor(38/2) + 1 byte + 1 byte = 1+19+1+1 = 22

therefore, 22 bytes is the max.

A trailing NULL in a database row consumes 0 bytes. Therefore 0 is the minimum.

You can see the effects of various values/sizes via:

  1* select x, dump(x) d, vsize(x) v from t tkyte_at_8.0> /

         X D                            V
---------- ------------------------- ----
         0 Typ=2 Len=1: 128             1
         1 Typ=2 Len=2: 193,2           2
       412 Typ=2 Len=3: 194,5,13        3

T was a table with 1 column (x number) and I put the values 0, 1, and 412 in there.

>Thomas Kyte <tkyte_at_us.oracle.com> wrote:
>> A copy of this was sent to Norris <johnnie_at_cooper.com.hk>
>> (if that email address didn't require changing)
>> On 16 Sep 1999 13:26:30 GMT, you wrote:
>
>>>May I know the number of bytes required to store the data type:
>>>
>>>NUMBER
>>>NUMBER (*,1)
>
>
>> <quote src=concepts manual>
>> 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 10 2 , 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).
>
>> 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
>
>> 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.
>
>> </quote>
>
>> So, the above 2 number formats will consume EXACTLY the same amount of variable
>> space given the same input numbers.
>
>> --
>> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Sep 17 1999 - 06:01:09 CDT