Decimal vs. int when using ORMs

Question

I have been using ORM tools/code generators and I see that they prefer using decimal to int values when mapping columns to properties. Is there any advantage of using decimal ?

The column type in database is the default Number which created as Number(38, 0) I believe.

Answer 1

NUMBER is NUMBER(38), which has far larger possible range than int (Int32) (and much larger than long, too). double has different rounding semantics, so decimal is preferred to avoid errors and issues. If the ORM can guarantee that the data fits inside int, it might be happier to use int.

Answer 2

Decimal is a 128-bit datatype. An Int32 is 32-bit, and is too small for general purpose, because tables commonly have row counts that overflow a 32-bit int. Some tools just default to a NUMBER(38) or the INTEGER alias in Oracle, which maps to the same, and some tools take the easy way out and use Decimal, while others try to map more closely to the corresponding value ranges.

Considering how large an Oracle NUMBER(38) can be, (38 significant digits is a large number), Decimal is the only safe option. But if you know you are storing sequential values, then an Int64 is practical enough, because even Decimal could potentially overflow with an Oracle NUMBER. Decimal can hold up to 79,228,162,514,264,337,593,543,950,335. That is "only" 29 significant digits and still can't hold a NUMBER(38) max value.

If you want a closer mapping, you need to use smaller precision NUMBER fields in Oracle. I use:

NUMBER(9) => Int32
NUMBER(18) => Int64
NUMBER(19+) => Decimal

in the data access code generators that I've written. ORMs may do the same, or not. Typically NUMBER(18) is adequate for any integer keys you will ever need.

If you aren't doing arithmetic with your primary keys, then I can't imagine any dis-advantage to using a Decimal type if you just want the "fire and forget" and never worry about a value that doesn't fit. In OLTP systems there is neglible performance difference between using a Decimal and an Int64, and Oracle doesn't care whether you define a field as NUMBER(1) or NUMBER(38) as far as data store, the NUMBER() type is a variable length type like VARCHAR and will only take up as much space as is needed for the particular value in each row. It is not a fixed length storage so in effect you are only constraining the potential value, not saving space.

SQL> insert into bbb values(1);

1 row created.

SQL> insert into bbb values(11111111);

1 row created.

SQL> insert into bbb values(1111111111111111111111111);

1 row created.

SQL> select i, vsize(i) from bbb;

         I   VSIZE(I)
---------- ----------
         1          2
  11111111          5
1.1111E+24         14