## Question and Answer

## You Asked

Oracle is under the impression that 1/19 * 19 - 1 = -6 x 10^(-40). You can replace 19 with 19 multiplied by any positive power of 100 and the problem remains. (When you multiply 19 by an odd power of 10, the answer is zero. For example, 1/1900 * 1900 - 1 = -6x10^(-40), but 1/190 * 190 - 1 = 0.) I understand it has to do with the way numbers are stored internally, but is there a simple way around it?

Note that this also happens in version 10.2.4

Note that this also happens in version 10.2.4

## and we said...

That happens with many numbers - it happens with all man made representation of numbers - it happens all of the time in computers (especially with floating point numbers - they are horrible)

(this isn't specific to Oracle, spreadsheets - all computer programs I've ever seen - everything is subject to this somewhere)

An Oracle number has up to 38 digits of precision. (A float has about 6 and a double about 13 - so the Oracle number is much more precise than those types)

The problem arises anytime in computer programs when you start doing things with really small numbers and really big numbers - we blow out the precision of the intermediate result - we cannot hold all of the digits.

The problem starts with 1/19. We cannot represent that number "perfectly"

(we run out of places to hold the decimal - that is not exactly 1/19, it is close). And when we multiply by 19 - we get into another bind - we have 38 digits of precision - but our numbers are so far apart - we'd need more than 38 to do it precisely so:

And now you see the issue. If we were to do this in floating point - it would work (with these numbers) by accident:

The only way I know "around it", is the old programming technique of not using "=", but using a tolerance. You cannot use another type of number (like a float or double) as they'll get errors as well - using different numbers.

instead of "if (x = 0) then"

you would "if (abs(x-0) < tolerance) then"

(this isn't specific to Oracle, spreadsheets - all computer programs I've ever seen - everything is subject to this somewhere)

An Oracle number has up to 38 digits of precision. (A float has about 6 and a double about 13 - so the Oracle number is much more precise than those types)

The problem arises anytime in computer programs when you start doing things with really small numbers and really big numbers - we blow out the precision of the intermediate result - we cannot hold all of the digits.

The problem starts with 1/19. We cannot represent that number "perfectly"

ops$tkyte%ORA11GR2> select 1/19 from dual; 1/19 ------------------------------------------------ 0.052631578947368421052631578947368421052600000

(we run out of places to hold the decimal - that is not exactly 1/19, it is close). And when we multiply by 19 - we get into another bind - we have 38 digits of precision - but our numbers are so far apart - we'd need more than 38 to do it precisely so:

ops$tkyte%ORA11GR2> select 1/19*19 from dual; 1/19*19 ------------------------------------------------ 0.999999999999999999999999999999999999999400000

And now you see the issue. If we were to do this in floating point - it would work (with these numbers) by accident:

ops$tkyte%ORA11GR2> select 1f/19f from dual; 1F/19F ------------------------------------------------ 0.052631579300000000000000000000000000000000000 <b>you see the massively reduced precision...</b> ops$tkyte%ORA11GR2> select 0.0526315793f * 19f from dual; 0.0526315793F*19F ------------------------------------------------ 1.000000000000000000000000000000000000000000000 <b>and it appears to work - but only because of the reduced precision - for you see, using more "precise" numbers:</b> ops$tkyte%ORA11GR2> select 0.0526315793 * 19 from dual; 0.0526315793*19 ------------------------------------------------ 1.000000006700000000000000000000000000000000000 <b>that isn't really 1, the error is actually much larger - but you lost it because you had a number with 11 digits to the right of the "." and a number with two digits to the left - the reduced precision arithmetic "lost" the error (by accident)

The only way I know "around it", is the old programming technique of not using "=", but using a tolerance. You cannot use another type of number (like a float or double) as they'll get errors as well - using different numbers.

instead of "if (x = 0) then"

you would "if (abs(x-0) < tolerance) then"

ops$tkyte%ORA11GR2> declare 2 l_float binary_float; 3 l_testf binary_float; 4 5 l_double binary_float; 6 l_testd binary_float; 7 begin 8 for i in 1 .. 100 9 loop 10 l_testf := i; 11 l_float := 1f/l_testf*l_testf-1f; 12 if ( l_float <> 0f ) then dbms_output.put_line( i|| ') l_float <> 0' ); end if; 13 14 l_testd := i; 15 l_double := 1d/l_testd*l_testd-1d; 16 if ( l_double <> 0d ) then dbms_output.put_line( i|| ') l_double <> 0' ); end if; 17 end loop; 18 end; 19 / 41) l_float <> 0 47) l_float <> 0 49) l_double <> 0 55) l_float <> 0 61) l_float <> 0 82) l_float <> 0 83) l_float <> 0 94) l_float <> 0 97) l_float <> 0 98) l_double <> 0 PL/SQL procedure successfully completed.

## and you rated our response

Is this answer out of date? If it is, please let us know via a Review

# Reviews

Tom,

nice answer.

I've ran into that issue on many occasions.

My problem stems from applications inserting the abnormally "accurate" number in the first place. The negotiated solution was to store only 6 significant digits (not decimal places).

I've only been able to do this by converting the number to a string in scientific notation then converting it back to a number.

eg

insert into t (data_value) values (

to_number( to_char( :x , '9.99999EEEE') )

);

Is there a function/better way to round to a given number of significant digits?

thanks

MK

nice answer.

I've ran into that issue on many occasions.

My problem stems from applications inserting the abnormally "accurate" number in the first place. The negotiated solution was to store only 6 significant digits (not decimal places).

I've only been able to do this by converting the number to a string in scientific notation then converting it back to a number.

eg

insert into t (data_value) values (

to_number( to_char( :x , '9.99999EEEE') )

);

Is there a function/better way to round to a given number of significant digits?

thanks

MK

Search Internet for document named "What Every Computer Scientist Should Know About Floating Point Arithmetic".

hmm, have you already seen

http://www.wolframalpha.com/input/?i=1%2F19+*+19+-+1+%3D+-6+x+10^%28-40%29

and

http://www.wolframalpha.com/input/?i=1%2F19+*+19+-+1+%3D+0

?

http://www.wolframalpha.com/input/?i=1%2F19+*+19+-+1+%3D+-6+x+10^%28-40%29

and

http://www.wolframalpha.com/input/?i=1%2F19+*+19+-+1+%3D+0

?

Does wolframalpha actually does the computation or is it an intelligence based algorithm that mimics human ability to be able to pick the best solution intuitively? Maybe it is doing 19/19=1 - 1 = 0 just like a person with some mathematical skill would do.

As well as defining table number columns appropriately and not just as a default NUMBER type, subtypes are a useful feature.

The MONEY_T subtype below will hold a large amount of money to two decimal places. You'll need to amend this if you plan to hold Tunisian Dinars (three decimal places) or Zimbabwean Dollars (ZWD10 million to USD4 in 2008...)

The MONEY_T subtype below will hold a large amount of money to two decimal places. You'll need to amend this if you plan to hold Tunisian Dinars (three decimal places) or Zimbabwean Dollars (ZWD10 million to USD4 in 2008...)

SET SERVEROUTPUT ON SIZE 1000000 DECLARE SUBTYPE MONEY_T IS NUMBER(25,2); SUBTYPE PERCENTAGE_T IS PLS_INTEGER RANGE 0 .. 100; gross MONEY_T := 102.7551; -- Rounded to 102.76 when entered into gross tax PERCENTAGE_T := 31; net MONEY_T; BEGIN DBMS_OUTPUT.PUT_LINE(gross); DBMS_OUTPUT.PUT_LINE(tax); net := gross/tax; DBMS_OUTPUT.PUT_LINE(gross/tax); -- Lots of decimal places DBMS_OUTPUT.PUT_LINE(net); -- Two decimal places as defined in subtype END; /

It is probably more confusing when you are using variables, and the variables just happen to both end up as 19. but defensive programing may be the answer

First - always use parenthesis to make your meaning crystal clear.

Second - Apply some equivalent math to avoid this completely

If you run this in TOAD like this:

select (((1/:x) * :y) - 1) from dual

where (((1/:x) * :y) - 1) = 0

You get no rows when you provide 19, 1900, 190000, 13, 1300

but if you run a mathematically equivalent version like this:

select ((1 * :y/:x) - 1) from dual

where ((1 * :y/:x) - 1) = 0

Which is really

select ((:y/:x) - 1) from dual

where ((:y/:x) - 1) = 0

So if you take out the unnecessary complexity it makes life simpler and gives that poor computer a break.

Just saying ...

Dave

First - always use parenthesis to make your meaning crystal clear.

Second - Apply some equivalent math to avoid this completely

If you run this in TOAD like this:

select (((1/:x) * :y) - 1) from dual

where (((1/:x) * :y) - 1) = 0

You get no rows when you provide 19, 1900, 190000, 13, 1300

but if you run a mathematically equivalent version like this:

select ((1 * :y/:x) - 1) from dual

where ((1 * :y/:x) - 1) = 0

Which is really

select ((:y/:x) - 1) from dual

where ((:y/:x) - 1) = 0

So if you take out the unnecessary complexity it makes life simpler and gives that poor computer a break.

Just saying ...

Dave

if i were to count upto 3000 by tens and i am on the nummber 50 how many more numbers do i need to compleate upto number 3000

funny !

don't have an Oracle DB under my hands just now, only my head, and I think you need only 295 steps more

don't have an Oracle DB under my hands just now, only my head, and I think you need only 295 steps more

SELECT SUM (1)

FROM DUAL

CONNECT BY 50 + (LEVEL) * 10 <= 3000

Sum(1)

---------

295

FROM DUAL

CONNECT BY 50 + (LEVEL) * 10 <= 3000

Sum(1)

---------

295

select (14341/100) from dual;

But I want only .41 so how it's get

But I want only .41 so how it's get