[ACCEPTED]-Decimal vs. int when using ORMs-llblgen
NUMBER is NUMBER(38), which has far larger possible range 5 than int (Int32) (and much larger than long, too). double has 4 different rounding semantics, so decimal is preferred 3 to avoid errors and issues. If the ORM can 2 guarantee that the data fits inside int, it might be 1 happier to use int.
Decimal is a 128-bit datatype. An Int32 38 is 32-bit, and is too small for general 37 purpose, because tables commonly have row 36 counts that overflow a 32-bit int. Some 35 tools just default to a NUMBER(38) or the 34 INTEGER alias in Oracle, which maps to the 33 same, and some tools take the easy way out 32 and use Decimal, while others try to map 31 more closely to the corresponding value 30 ranges.
Considering how large an Oracle NUMBER(38) can 29 be, (38 significant digits is a large number), Decimal 28 is the only safe option. But if you know 27 you are storing sequential values, then 26 an Int64 is practical enough, because even 25 Decimal could potentially overflow with 24 an Oracle NUMBER. Decimal can hold up to 23 79,228,162,514,264,337,593,543,950,335. That is "only" 29 significant digits and 22 still can't hold a NUMBER(38) max value.
If 21 you want a closer mapping, you need to use 20 smaller precision NUMBER fields in Oracle. I 19 use:
NUMBER(9) => Int32
NUMBER(18) => Int64
NUMBER(19+) => Decimal
in the data access code generators that 18 I've written. ORMs may do the same, or not. Typically 17 NUMBER(18) is adequate for any integer keys 16 you will ever need.
If you aren't doing arithmetic 15 with your primary keys, then I can't imagine 14 any dis-advantage to using a Decimal type 13 if you just want the "fire and forget" and 12 never worry about a value that doesn't fit. In 11 OLTP systems there is neglible performance 10 difference between using a Decimal and an 9 Int64, and Oracle doesn't care whether you 8 define a field as NUMBER(1) or NUMBER(38) as 7 far as data store, the NUMBER() type is 6 a variable length type like VARCHAR and 5 will only take up as much space as is needed 4 for the particular value in each row. It 3 is not a fixed length storage so in effect 2 you are only constraining the potential 1 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
More Related questions
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.