Storage of NUMBER datatype in Oracle

This seems to come up at work every few years, and it is interesting and worth noting if you develop or administer Oracle databases. The precision of a number datatype in Oracle is only there to limit the precision, not to limit how much space a number can take up. Oracle stores numbers not as a string as you might initially think when you look at a definition, say NUMBER(10). Oracle stores numbers in scientific notation – “Each value is stored in scientific notation, with 1 byte used to store the exponent.” See the manual for more details.

Due to storing a number this way, it is possible for a number that is larger numerically than another number to actually take up  less storage. See this example I prepared at the work the other day:

SQL> create table jed (col1 number(5), col2 number(20));
Table created.
SQL> insert into jed values (34258,100000000000);
1 row created.
SQL> select col1, vsize(col1), col2, vsize(col2) from jed;
      COL1 VSIZE(COL1)                COL2 VSIZE(COL2)
---------- ----------- ------------------- -----------
     34258           4        100000000000           2
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s