Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

Question

Oracle has a favorite numerical class called NUMBER. It is a 22 byte value that just seems ludicrous in an index. I have some values that would easily fit in a 6 byte integer and would therefore fit in a nice native datatype that is 64 bits long. However oracle keeps making my numbers 22 bytes long.

The only place I can see this is if I put the value in an index, and there is shows me the value is a 22 byte number.

I understand it is nice to have numbers that don't have rounding errors, I can appreciate that, but I just want an integer that can be used as a ForeignKey to another table, and rounding won't be an issue.

Mysql, Postgres, MSSQL all have variants that let me specify a datasize (via some mechanism). Oracle wants to map almost every other numeric type back to NUMBER(size, precision), but no matter what values I use for size or precision, oracle insists the number takes up 22 bytes in my index. That is nearly 3x longer than I need and effectively cuts the M of my btree.

Oracle does have BINARY_FLOAT and BINARY_DOUBLE, it even has a ROWID which is nothing like an integer at all.

Is there some secret sauce that Oracle has that makes NUMBER uber efficient? Are there secret docs somewhere that show datatypes that map to native datatypes?

Answer 1

However oracle keeps making my numbers 22 bytes long

This assumption is wrong. Values stored in a NUMBER column take up only as much space as needed.

Quote from the manual

A NUMBER value requires from 1 to 22 bytes

^{(emphasis mine)}

This can be validated using the VSIZE() function:

create table foo (id number(22), some_value number(22,8));

insert into foo values (1,  1.1);
insert into foo values (9999999999, 99999.123);
insert into foo values (9999999999999999, 9999999999999.6789);

select id, vsize(id), some_value, vsize(some_value) 
from foo;

will return:

ID               | VSIZE(ID) | SOME_VALUE       | VSIZE(SOME_VALUE)
-----------------+-----------+------------------+------------------
               1 |         2 |              1.1 |                 3
      9999999999 |         6 |         99999.12 |                 6
9999999999999999 |         9 | 9999999999999.68 |                10

Answer 2

See here ixora.

• Number has variable length, like string.
• each digit occupies one nibble (4bits, half byte)
• NUMBER implementation is platform independent
• you can validate size of number value in a column by calling dump() function

PS: Oracle also supports native intergers like PLS_INTEGER, but these can only be used in PL/SQL context only.