Big numbers in Oracle
Why it works
select 10000000000000000000000000000000000000000*9 from dual --41 digit number
Output:
90000000000000000000000000000000000000000
but this gives the wrong result
select 10000000000000000000000000000000000000000+1 from dual --41 digit number
Output:
10000000000000000000000000000000000000000
I can't figure out why Oracle can multiply a large number by 9 but not add 1 to it!
source to share
As the comments point out, you are falling within the precision of Oracle's number. In other words, Oracle has a limit of significant digits that it can handle (even in memory) before causing them to round up and start losing precision, which is exactly what you observed.
While your example makes the behavior seem counter-intuitive, it makes sense if we illustrate it by seeing how we would translate these numbers into scientific notation.
So, for the number 10000000000000000000000000000000000000000
it can be represented briefly as follows: 1E+40
.
However, to represent the number 10000000000000000000000000000000000000001
, there really isn't a concise way to represent it. Unless I want to lose precision, in scientific notation, the shortest way to represent it 10000000000000000000000000000000000000001E+0
. But this clearly takes up much more space than a simple one 1E+40
.
On the contrary, the number 90000000000000000000000000000000000000000
can be represented concisely as follows: 9E+40
.
So yes, the last number is much larger than the previous one, but Oracle has no problem representing it with precision because it doesn't have significant digits.
source to share