CODEWITHSUNDEEP
sqloracle-databaseoracle10glargenumber
54l3d
54l3d

Reputation: 3973

Large numbers in Oracle

Why this works

select 10000000000000000000000000000000000000000*9 from dual --41 digit number

Output:

90000000000000000000000000000000000000000

but this gives uncorrect result

select 10000000000000000000000000000000000000000+1 from dual --41 digit number

Output:

10000000000000000000000000000000000000000

I cant understand why Oracle can multiply a large number by 9 but fail to add 1 to it !

Upvotes: 3

Views: 1484

Answers (1)

sstan
sstan

Reputation: 36483

As the comments are hinting at, you are hitting the limits of Oracle's number precision. In other words, Oracle has a limit of significant digits it can handle (even in memory) before being forced to perform some rounding and start losing precision, which is exactly what you observed.

Although your example makes the behavior seem counter-intuitive, it does make 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 concisely in the following form: 1E+40.

However, in order to represent the number 10000000000000000000000000000000000000001, there really is no concise way to represent that. If I don't want to lose precision, in scientific notation, the shortest way to represent that is 10000000000000000000000000000000000000001E+0. But that clearly takes a lot more space than the simple 1E+40.

In contrast, the number 90000000000000000000000000000000000000000 can be represented concisely as so: 9E+40.

So yes, that last number is much larger than the previous one, but Oracle has no problem representing it with precision because it doesn't have many significant digits.

Upvotes: 2

Related Questions