Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Roundoff error question

Roundoff error question

From: Don Del Grande <delgrandedp_at_navsea.navy.mil>
Date: 27 Oct 2005 08:43:50 -0700
Message-ID: <1130427830.379178.319050@g49g2000cwa.googlegroups.com>


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;

SELECT * FROM roundoff_error;

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.09999999999999999999999999999999999995
Subtract 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?

Received on Thu Oct 27 2005 - 10:43:50 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US