we store numbers in a packed decimal like format which gives us 2 digits per byte - since we have 20 of them in this base 100 notation - that gives us up to 39 or 40 digits of precision.
Here is a longer writeup on numbers from my book Expert Oracle database Architecture:
Number Types
Oracle 10g and above supports three native datatypes suitable for storing numbers. Oracle9i Release 2 and earlier support exactly one native datatype suitable for storing numeric data. In this list, the NUMBER type is supported by all releases, and the subsequent two types are new datatypes supported only in Oracle 10g and above:
* NUMBER: The Oracle NUMBER type is capable of storing numbers with an extremely large degree of precision—38 digits of precision, in fact. The underlying data format is similar to a “packed decimal” representation. The Oracle NUMBER type is a variable length format from 0 to 22 bytes in length. It is appropriate for storing any number as small as 10e-130 and numbers up to but not including 10e126. This is by far the most common NUMBER type in use today.
* BINARY_FLOAT: This is an IEEE native single-precision floating-point number. On disk it will consume 5 bytes of storage: 4 fixed bytes for the floating-point number and 1 length byte. It is capable of storing numbers in the range of ~ ± 1038.53 with 6 digits of precision.
* BINARY_DOUBLE: This is an IEEE native double-precision floating-point number. On disk it will consume 9 bytes of storage: 8 fixed bytes for the floating-point number and 1 length byte. It is capable of storing numbers in the range of ~ ± 10308.25 with 13 digits of precision.
As you can see from this quick overview, the Oracle NUMBER type has significantly larger precision than the BINARY_FLOAT and the BINARY_DOUBLE types, but a much smaller range than the BINARY_DOUBLE. That is, you can store numbers very precisely with many significant digits in a NUMBER type, but you can store much smaller and larger numbers in the BINARY_FLOAT and BINARY_DOUBLE types. As a quick example, we can create a table with the various datatypes in them and see what is stored given the same inputs:
ops$tkyte@ORA11GR2> create table t
2 ( num_col number,
3 float_col binary_float,
4 dbl_col binary_double
5 )
6 /
Table created.
ops$tkyte@ORA11GR2> insert into t ( num_col, float_col, dbl_col )
2 values ( 1234567890.0987654321,
3 1234567890.0987654321,
4 1234567890.0987654321 );
1 row created.
ops$tkyte@ORA11GR2> set numformat 99999999999.99999999999
ops$tkyte@ORA11GR2> select * from t;
NUM_COL FLOAT_COL DBL_COL
------------------------ ------------------------ ------------------------
1234567890.09876543210 1234567940.00000000000 1234567890.09876540000
Note that the NUM_COL returns the exact number we provided as input. There are fewer than 38 significant digits in the input number (I supplied a number with 20 significant digits), so the exact number is preserved. The FLOAT_COL, however, using the new BINARY_FLOAT type, was not able to accurately represent this number. In fact, it preserved only 7 digits accurately. The DBL_COL faired much better, accurately representing the number in this case out to 17 digits. Overall, though, this should be a good indication that the BINARY_FLOAT and BINARY_DOUBLE types will not be appropriate for financial applications! If you play around with different values, you’ll see different results:
ops$tkyte@ORA11GR2> delete from t;
1 row deleted.
ops$tkyte@ORA11GR2> insert into t ( num_col, float_col, dbl_col )
2 values ( 9999999999.9999999999,
3 9999999999.9999999999,
4 9999999999.9999999999 );
1 row created.
ops$tkyte@ORA11GR2> select * from t;
NUM_COL FLOAT_COL DBL_COL
------------------------ ------------------------ ------------------------
9999999999.99999999990 10000000000.00000000000 10000000000.00000000000
Once again, the NUM_COL accurately represented the number, but the FLOAT_COL and DBL_COL cannot. This does not mean that the NUMBER type is able to store things with “infinite” accuracy/precision—just that it has a much larger precision associated with it. It is easy to observe similar results from the NUMBER type:
ops$tkyte@ORA11GR2> delete from t;
1 row deleted.
ops$tkyte@ORA11GR2> insert into t ( num_col )
2 values ( 123 * 1e20 + 123*1e-20 ) ;
1 row created.
ops$tkyte@ORA11GR2> set numformat 999999999999999999999999.999999999999999999999999
ops$tkyte@ORA11GR2> select num_col, 123*1e20, 123*1e-20 from t;
NUM_COL
--------------------------------------------------
123*1E20
--------------------------------------------------
123*1E-20
--------------------------------------------------
12300000000000000000000.000000000000000000000000
12300000000000000000000.000000000000000000000000
.000000000000000001230000
As you can see, when we put together a very large number (123*1e20) and a very small number (123*1e-20), we lost precision because this arithmetic requires more than 38 digits of precision. The large number by itself can be faithfully represented, as can the small number, but the result of the larger plus the smaller cannot. We can verify this is not just a display/formatting issue as follows:
ops$tkyte@ORA11GR2> select num_col from t where num_col = 123*1e20;
NUM_COL
--------------------------------------------------
12300000000000000000000.000000000000000000000000
The value in NUM_COL is equal to 123*1e20, and not the value we attempted to insert.
NUMBER Type Syntax and Usage
The syntax for the NUMBER type is straightforward:
NUMBER( p,s )
where P and S are optional and are used to specify the following:
* Precision, or the total number of digits. By default, the precision is 38 and has valid values in the range of 1 to 38. The character * may be used to represent 38 as well.
* Scale, or the number of digits to the right of the decimal point. Valid values for the scale are –84 to 127, and its default value depends on whether or not the precision is specified. If no precision is specified, then scale defaults to the maximum range. If a precision is specified, then scale defaults to 0 (no digits to the right of the decimal point). So, for example, a column defined as NUMBER stores floating-point numbers (with decimal places), whereas a NUMBER(38) stores only integer data (no decimals), since the scale defaults to 0 in the second case.
You should consider the precision and scale to be edits for your data—data integrity tools in a way. The precision and scale do not affect at all how the data is stored on disk, only what values are permitted and how numbers are to be rounded. For example, if a value exceeds the precision permitted, Oracle returns an error:
ops$tkyte@ORA11GR2> create table t ( num_col number(5,0) );
Table created.
ops$tkyte@ORA11GR2> insert into t (num_col) values ( 12345 );
1 row created.
ops$tkyte@ORA11GR2> insert into t (num_col) values ( 123456 );
insert into t (num_col) values ( 123456 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
So, you can use the precision to enforce some data integrity constraints. In this case, NUM_COL is a column that is not allowed to have more than five digits.
The scale, on the other hand, is used to control “rounding” of the number, for example:
ops$tkyte@ORA11GR2> create table t ( msg varchar2(10), num_col number(5,2) );
Table created.
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '123.45', 123.45 );
1 row created.
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '123.456', 123.456 );
1 row created.
ops$tkyte@ORA11GR2> select * from t;
MSG NUM_COL
---------- ----------
123.45 123.45
123.456 123.46
Notice how the number 123.456, with more than five digits, succeeded this time. That is because the scale we used in this example was used to round 123.456 to two digits, resulting in 123.46, and then 123.46 was validated against the precision, found to fit, and inserted. However, if we attempt the following insert, it fails:
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '1234', 1234 );
insert into t (msg,num_col) values ( '1234', 1234 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
because the number 1234.00 has more than five digits in total. When you specify the scale of 2, at most three digits may be to the left of the decimal place and two to the right. Hence that number does not fit. The NUMBER(5,2) column can hold all values between 999.99 and –999.99.
It may seem strange to allow the scale to vary from –84 to 127. What purpose could a negative scale fulfill? It allows you to round values to the left of the decimal place. Just as the NUMBER(5,2) rounded values to the nearest .01, so a NUMBER(5,-2) would round to the nearest 100, for example:
ops$tkyte@ORA11GR2> create table t ( msg varchar2(10), num_col number(5,-2) );
Table created.
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '123.45', 123.45 );
1 row created.
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '123.456', 123.456 );
1 row created.
ops$tkyte@ORA11GR2> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
The numbers were rounded to the nearest 100. We still have five digits of precision, but there are now seven digits (including the trailing two 0s) permitted to the left of the decimal point:
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '1234567', 1234567 );
1 row created.
ops$tkyte@ORA11GR2> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
1234567 1234600
ops$tkyte@ORA11GR2> insert into t (msg,num_col) values ( '12345678', 12345678 );
insert into t (msg,num_col) values ( '12345678', 12345678 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column
So, the precision dictates how many digits are permitted in the number after rounding, using the scale to determine how to round. The precision is an integrity constraint, whereas the scale is an “edit.”
It is interesting and useful to note that the NUMBER type is, in fact, a variable length datatype on disk and will consume between 0 and 22 bytes of storage. Many times, programmers consider a numeric datatype to be a fixed-length type—that is what they typically see when programming with 2- or 4-byte integers and 4- or 8-byte floats. The Oracle NUMBER type is similar to a variable length character string. We can see what happens with numbers that contain differing amounts of significant digits. We’ll create a table with two NUMBER columns and populate the first column with many numbers that have 2, 4, 6, … 28 significant digits. Then, we’ll simply add 1 to each of them:
ops$tkyte@ORA11GR2> create table t ( x number, y number );
Table created.
ops$tkyte@ORA11GR2> insert into t ( x )
2 select to_number(rpad('9',rownum*2,'9'))
3 from all_objects
4 where rownum <= 14;
14 rows created.
ops$tkyte@ORA11GR2> update t set y = x+1;
14 rows updated.
Now, if we use the built-in VSIZE function that shows how much storage the column takes, we can review the size differences between the two numbers in each row:
ops$tkyte@ORA11GR2> set numformat 99999999999999999999999999999
ops$tkyte@ORA11GR2> column v1 format 99
ops$tkyte@ORA11GR2> column v2 format 99
ops$tkyte@ORA11GR2> select x, y, vsize(x) v1, vsize(y) v2
2 from t order by x;
X Y V1 V2
------------------------------ ------------------------------ --- ---
99 100 2 2
9999 10000 3 2
999999 1000000 4 2
99999999 100000000 5 2
9999999999 10000000000 6 2
999999999999 1000000000000 7 2
99999999999999 100000000000000 8 2
9999999999999999 10000000000000000 9 2
999999999999999999 1000000000000000000 10 2
99999999999999999999 100000000000000000000 11 2
9999999999999999999999 10000000000000000000000 12 2
999999999999999999999999 1000000000000000000000000 13 2
99999999999999999999999999 100000000000000000000000000 14 2
9999999999999999999999999999 10000000000000000000000000000 15 2
14 rows selected.
We can see that as we added significant digits to X, the amount of storage required took increasingly more room. Every two significant digits added another byte of storage. But a number just one larger consistently took 2 bytes. When Oracle stores a number, it does so by storing as little as it can to represent that number. It does this by storing the significant digits, an exponent used to place the decimal place, and information regarding the sign of the number (positive or negative). So, the more significant digits a number contains, the more storage it consumes.
That last fact explains why it is useful to know that numbers are stored in varying width fields. When attempting to size a table (e.g., to figure out how much storage 1,000,000 rows would need in a table), you have to consider the NUMBER fields carefully. Will your numbers take 2 bytes or 20 bytes? What is the average size? This makes accurately sizing a table without representative test data very hard. You can get the worst-case size and the best-case size, but the real size will likely be some value in between.