Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Don.

Asked: February 02, 2011 - 11:52 am UTC

Answered by: Tom Kyte - Last updated: October 27, 2020 - 1:37 am UTC

Category: Database - Version: 11.2.1

Viewed 100+ times

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

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"

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

  (10 ratings)

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

Reviews

February 02, 2011 - 4:56 pm UTC

Reviewer: Micheal Kutz from Greensboro, NC

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

Tom Kyte

Followup  

February 03, 2011 - 3:01 pm UTC

ops$tkyte%ORA11GR2> set numformat 9999.99999999999
ops$tkyte%ORA11GR2> select round(1/19,6) from dual;

    ROUND(1/19,6)
-----------------
     .05263200000

Further reading

February 03, 2011 - 1:43 am UTC

Reviewer: Jozef Babjak from Slovakia

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


"all computer programs I've ever seen - everything is subject to this somewhere"

February 03, 2011 - 1:57 am UTC

Reviewer: Sokrates

Tom Kyte

Followup  

February 03, 2011 - 3:20 pm UTC

so, if you use floats in Oracle - it does it "right" as well. When dealing with numbers of excessively different scale/precision, you'll run into issues.

To Sokrates

February 03, 2011 - 2:05 pm UTC

Reviewer: A reader

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.

Subtypes

February 04, 2011 - 9:07 am UTC

Reviewer: Vince Palser from London UK

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...)

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;
/

Defensive Programming

February 04, 2011 - 3:17 pm UTC

Reviewer: Dave from Bloomingotn, MN USA

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

Math

February 18, 2011 - 8:06 pm UTC

Reviewer: Rachel Sadykova from USA

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

Tom Kyte

Followup  

February 20, 2011 - 12:13 pm UTC

eh?

given we are a database forum, let's answer it with sql and brute force :)

ops$tkyte%ORA11GR2> select count(*)
  2    from (
  3  select null
  4    from dual
  5  connect by 50+(level-1)*10 <= 3000
  6  )
  7  /

  COUNT(*)
----------
       296


295 ?

February 20, 2011 - 12:49 pm UTC

Reviewer: Sokrates

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

Here you go....

February 22, 2011 - 3:55 pm UTC

Reviewer: Brat from Michigan

SELECT SUM (1)
FROM DUAL
CONNECT BY 50 + (LEVEL) * 10 <= 3000


Sum(1)
---------
295

get only precision

October 23, 2020 - 11:35 am UTC

Reviewer: smith

select (14341/100) from dual;
But I want only .41 so how it's get
Connor McDonald

Followup  

October 27, 2020 - 1:37 am UTC

SQL> select mod(14341/100,1) from dual;

MOD(14341/100,1)
----------------
             .41

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here