The Oracle (tm) Users' Co-Operative FAQ
How much space does a number take up.
| Author's name: Jonathan Lewis Author's Email: Jonathan@jlcomp.demon.co.uk |
Date written: 26th July 2001 Oracle version(s): 7.3 - 8.1.7.0 |
| It is possible to define a number so that it is stored in the database with 38 digits of precision. If I do this, how much space does a number really use if it doesn't actually use this degree of precision. Strangely, the internal storage for number (dba_tab_columns) suggests that the worst case usage for a number is 22 bytes, so how much space does it really take to store a number ?. |
Oracle uses an internal 'base 100 encoding' format for storing numbers, which stores two digits of precision per byte, an extra byte that holds both the sign and the 'mantissa' indicating where to put the decimal point in relation to the digits of precision, and for negative numbers a terminating byte holding the value 0x66.
Consequently the actual space used by a number depends on the number of significant digits that appear in the number and the sign of the number, so the numbers 1, 100, 10,000 and 1,000,000 all take two bytes, and -1, -100, -10,000, -1,000,000 will take 3 bytes , whereas 1,234,567 will require 5 bytes, and -1,234,567 will require 6 bytes.
| Value | Representation | Bytes Stored |
| 1 | 1 x power(100,0) | c1, 2 |
| 100 | 1 x power(100,1) | c2, 2 |
| 10,000 | 1 x power(100,2) | c3, 2 |
| 1,000,000 | 1 x power(100,3) | c4, 2 |
| -1 | -1 x power(100,0) | 3e, 64, 66 |
| -100 | -1 x power(100,1) | 3d, 64, 66 |
| -10,000 | -1 x power(100,2) | 3c, 64, 66 |
| -1,000,000 | -1 x power(100,3) | 3b, 64, 66 |
| 1,234,567 | 1.234,567 x power(100,3) | c4, 2, 18, 2e, 44 |
| -1,234,567 | -1.234,567 x power(100,3) | 3b, 64, 4e, 38, 22, 66 |
Although there are special cases, as indicated by the values for 1, 100, 10,000, and 1,000,000 above, the rules tell us that the typical N-digit positive number will have require 1 + ceil(N/2) bytes, and a negative number will required 2 + ceil(N/2) bytes, where ceil() is found by rounding up to the next integer where necessary.
For example, most of the (six digit) numbers from 100,000 to 199,999 will encode to 4 bytes i.e.1 + (6/2), whereas most of the (nine digit) numbers from 100,000,000 to 100,099,999 will encode to 6 bytes i.e.1 + ceil(9/2) = 1 + 5. You can run the following SQL (as SYS because of the reference to the X$ object) to verify the second result:
select len, count(*) from ( select vsize(rownum + 99999999) len from x$ksmmem where rownum <= 100000 ) group by len ;
Giving results:
LEN COUNT(*)
--------- ---------
2 1
4 9
5 990
6 99000
Don't forget that when stored in a row, each numeric column will also require the standard one-byte row-length overhead
Further reading: N/A
