Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: NUMBER Storage

Re: NUMBER Storage

From: Norris <johnnie_at_cooper.com.hk>
Date: 18 Sep 1999 05:55:57 GMT
Message-ID: <7rv9hd$1rn3$1@adenine.netfront.net>


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 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.
For every null data type which is not trailing will consume 1 byte, am I right?

> 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

-- 
- Have several nice days...
- Opinions are mine and do not necessarily reflect those of the Corp.
http://www.ntfaq.com
http://www.cyberport.com/~tangent/programming/winsock/ Received on Sat Sep 18 1999 - 00:55:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US