Oracle Database SQL Language Reference states, "Each NUMBER value requires from 1 to 22 bytes." I was curious how much space a NUMBER(10, 0) would take, so I did:
SQL> create table t (a number(10, 0))
2 /
Table created.
SQL> select column_name, data_length
2 from user_tab_columns
3 where table_name = 'T'
4 /
COLUMN_NAME DATA_LENGTH
------------------------------ -----------
A 22
I expected that a NUMBER(10, 0) would take less than the full 22 bytes to store. At this point I have three theories about what is going on but have not be able to prove or disprove any to my own satisfaction: 1) NUMBER in both NUMBER and NUMBER(p, s) forms is stored in a variable width format. That storing 1 versus 9999 take different amounts of space on the disk and that storing 1 in a NUMBER(1) column takes just as much space as storing 1 in a NUMBER column. 2) Numbers are stored on the disk in 22 byte chunks, which appears to be contridicted by the documentation. 3) I don't understand what user_tab.columns.data_length means.
Using the DUMP function supports theory 1:
SQL> insert into t values(0);
1 row created.
SQL> insert into t values(9999999999);
1 row created.
SQL> insert into t values(-9999999999);
1 row created.
SQL> select substr(to_char(a), 1, 12), substr(dump(a), 1, 20)
2 from t
3 /
SUBSTR(TO_CH SUBSTR(DUMP(A),1,20)
------------ --------------------
0 Typ=2 Len=1: 128
9999999999 Typ=2 Len=6: 197,100
-9999999999 Typ=2 Len=7: 58,2,2,
(The TO_CHAR and SUBSTR are for formatting. Still learning SQL*Plus)
Is dump giving me the on disk representation? If so, then can I find the maximum space takesn by:
select dump(-9999.999) from dual;
Where the number passed to dump is the greatest negative number that fits in number(p, s)? (p nines total, with decimal place correct for the given s)
If not, how do I determine for a given NUMBER(p, s) how many (maximum) bytes is it going to take?
-- Shannon
All number types are stored as a varying length field from 0 to 22 bytes in length.
From Expert Oracle Database Architecture:
<quote>
It is interesting and useful to note that the NUMBER type is in fact a varying length data type 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 varying 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 then populate the first column with many numbers that have 2, 4, 6, ¿ 38 significant digits. Then, we¿ll simply add 1 to each of them:
ops$tkyte@ORA10GR1> create table t ( x number, y number );
Table created.
ops$tkyte@ORA10GR1> insert into t ( x )
2 select to_number(rpad('9',rownum*2,'9'))
3 from all_objects
4 where rownum <= 19;
19 rows created.
ops$tkyte@ORA10GR1> update t set y = x+1;
19 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@ORA10GR1> select x, y, vsize(x), vsize(y)
2 from t order by x;
X Y VSIZE(X) VSIZE(Y)
---------- ---------- ---------- ----------
99 100 2 2
9999 10000 3 2
999999 1000000 4 2
99999999 100000000 5 2
9999999999 1.0000E+10 6 2
1.0000E+12 1.0000E+12 7 2
1.0000E+14 1.0000E+14 8 2
1.0000E+16 1.0000E+16 9 2
1.0000E+18 1.0000E+18 10 2
1.0000E+20 1.0000E+20 11 2
1.0000E+22 1.0000E+22 12 2
1.0000E+24 1.0000E+24 13 2
1.0000E+26 1.0000E+26 14 2
1.0000E+28 1.0000E+28 15 2
1.0000E+30 1.0000E+30 16 2
1.0000E+32 1.0000E+32 17 2
1.0000E+34 1.0000E+34 18 2
1.0000E+36 1.0000E+36 19 2
1.0000E+38 1.0000E+38 20 2
19 rows selected.
SQLPlus used its default format ¿ don¿t let the scientific notation confuse you, all of the digits are there, we could use SET NUMFORMAT to see them all. However, we can see that as we added significant digits to X ¿ the amount of storage required took increasingly more room. Every 2 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 in order 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 digits a number contains, the more storage it consumes.
That last fact is why it is useful to know that numbers are stored in varying width fields. When attempting to size a table (to figure out how much storage 1,000,000 rows would need in a table for example), 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.
</quote>
so, your data will consume between 0 and 6 bytes:
ops$tkyte%ORA11GR1> select vsize(x*10+9), vsize(x+1) from (select 999999999 x from dual);
VSIZE(X*10+9) VSIZE(X+1)
------------- ----------
6 2