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_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?