0

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?

  • 3
    Oracle documentation (Datatypes, Internal Numeric Format) suggests that a NUMBER(6,0) would use only 4 bytes. – ypercubeᵀᴹ Apr 1 '15 at 12:26
  • If you build an index with one of these Number objects. The index indicates it takes up 22 bytes, regardless of the size and precision values you put into the declaration. While it might only take up 4 bytes in the the data, the index seems to be a completely different story. – boatcoder Apr 2 '15 at 17:45
2

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
  • But why do away with native values? What is the point? variable length numbers aren't needed in this case and would seem to be an anti-pattern for speed. – boatcoder Apr 1 '15 at 16:29
  • 1
    I guess Oracle thought the space saving is more important than the speed (if there is one at all). In other database you need to choose a different data type if you want to save space when storing e.g. values from 1 to 100. I think variable length numbers make absolute sense. Think about the space a table with 20 number columns needs when you to store 1 billion rows between 1 and 1000 and 1000 rows with a value 999999999999 – a_horse_with_no_name Apr 1 '15 at 16:37
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.

  • PLS_INTEGER seems to have a mapping to Number(p,s) – boatcoder Apr 1 '15 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.