Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Roundoff error question
I am trying to figure out exactly how ORACLE (I am currently using 8i)
handles arithmetic with NUMBERs. I have some idea how numbers are
stored internally, but I can't figure out how a particular operation
gets a particular result.
Here's what I have:
(Autocommit is on)
CREATE TABLE roundoff_error (N NUMBER); INSERT INTO roundoff_error(N) values(1/190);
UPDATE roundoff_error SET N = N * 10; UPDATE roundoff_error SET N = N * 19; UPDATE roundoff_error SET N = N - 1;
This returns -6 x 10^(-40).
However, I get something slightly different:
1/190 = 100^(-1) x 0.52631578947368421052631578947368421053 (rounded to
the 38th significant digit).
Multiplying by 10, an extra 0 needs to be added on the left to maintain
the proper exponent, so the last significant digit is lost:
100^0 x 0.05263157894736842105263157894736842105
Multiply this by 19:
100^0 x 0.09999999999999999999999999999999999995Subtract 1; the result in -5 x 10^(-38).
Did I miss something painfully obvious?
Did I make a mistaken assumption somewhere - for example, am I right in
assuming NUMBERs are stored with an implied zero to the left of the
decimal point? (e.g. 1 is actually stored as 100^1 * 0.01?) Would
that affect the result?