Re: NUMBER Storage

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 16 Sep 1999 09:30:29 -0400
Message-ID: <dfDgN4XzPtjUdBRgQuTUDWLPv3U5@4ax.com>

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 Received on Thu Sep 16 1999 - 08:30:29 CDT