Datatypes - Expert Oracle Database Architecture - page 530

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:
EODA@ORA12CR1> create table t ( x number, y number );
Table created.
EODA@ORA12CR1> insert into t ( x )
2 select to_number(rpad('9',rownum*2,'9'))
3 from all_objects
4 where rownum <= 14;
14 rows created.
EODA@ORA12CR1> 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:
EODA@ORA12CR1> set numformat 99999999999999999999999999999
EODA@ORA12CR1> column v1 format 99
EODA@ORA12CR1> column v2 format 99
EODA@ORA12CR1> 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.