Other Oracle database topics - Roundoff error question

Author

Roundoff error question

Don Del Grande

2005-10-27, 11:23 am

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. 52631578947368421052
631578947368421053 (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. 05263157894736842105
263157894736842105
Multiply this by 19:
100^0 x 0. 09999999999999999999
999999999999999995
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?

-- Don

Mark D Powell

2005-10-27, 11:23 am

I have added a select after each update. The difference is where you
have .09.. in your calculations the query shows a value of 1.

UT1 > set numwidth 38
UT1 > CREATE TABLE roundoff_error (N NUMBER);

Table created.

UT1 > INSERT INTO roundoff_error(N) values(1/190);

1 row created.

UT1 > SELECT * FROM roundoff_error;

N
--------------------------------------
.. 00526315789473684210
52631578947368421

UT1 > UPDATE roundoff_error SET N = N * 10;

1 row updated.

UT1 > SELECT * FROM roundoff_error;

N
--------------------------------------
.. 05263157894736842105
26315789473684211

UT1 > UPDATE roundoff_error SET N = N * 19;

1 row updated.

UT1 > SELECT * FROM roundoff_error;

N
--------------------------------------
1

UT1 > UPDATE roundoff_error SET N = N - 1;

1 row updated.

UT1 > SELECT * FROM roundoff_error;

N
--------------------------------------
-6. 00000000000000000000
00000000000E-40

HTH -- Mark D Powell --

Don Del Grande

2005-10-27, 11:23 am

Mark D Powell wrote:
> I have added a select after each update. The difference is where you
> have .09.. in your calculations the query shows a value of 1.
>
> UT1 > CREATE TABLE roundoff_error (N NUMBER);
> UT1 > INSERT INTO roundoff_error(N) values(1/190);
> UT1 > UPDATE roundoff_error SET N = N * 10;
> UT1 > SELECT * FROM roundoff_error;
>
> N
> --------------------------------------
> . 05263157894736842105
26315789473684211

At this point, I get:
0. 05263157894736842105
26315789473684210526

(I used numwidth 50 to make sure I got all of the digits)

Now I see where ORACLE is getting -6 x 10^(-40).
However, I don't understand where the 39th significant digit is coming
from - I thought ORACLE only maintained 38 significant digits in
NUMBERs.

Can anybody tell me why there are 39 significant digits? Is this a
"hidden feature" of some sort of ORACLE where it will maintain a 39th
digit if the first digit in the first stored byte is zero (so it would
use 20 bytes in the mantissa - otherwise only 37 significant digits
would be maintained)?

-- Don

Sponsored Links