Problems with Power function 8.1.7.0

From: roobaron <member_at_dbforums.com>
Date: Thu, 05 Sep 2002 01:09:36 +0000
Message-ID: <1776187.1031188176@dbforums.com>

Hi,

We have been wrestling with a weird problem with the power function in Oracle 8.1.7.0 running on Tru64 Unix.
Basically the sql runs and then hangs between 3500-5000 rows...

Does someone know how oracle implements the power function internally?

One of our statistical gurus here came up with a workaround using this function, see below.

Here was the offending piece of SQL

select ...
CASE WHEN (months_between(sysdate, a.SALE_DATE_RECENT) > 12) THEN a.SALE_PRICE_RECENT * POWER((1 + b.rate), months_between(sysdate, a.SALE_DATE_RECENT))
from ...

Any got any ideas?

I have trawled the internet and metalink looking for bugs or other people having this problem.

We are planning to patch to 8.1.7.4 in the next couple of weeks.

Have Fun

Paul

Workaround Power function.
"Since the value of b.rate << 1, I was able to write a PL/SQL function that uses a Taylor series approximation of the power function. The code for this function is:

CREATE OR REPLACE
FUNCTION dm_power (base REAL, exponent REAL) RETURN REAL IS

accumulator REAL;
increment   REAL;
x           REAL;
n           INTEGER;

BEGIN
accumulator := 1;
increment := 1;
  x := base - 1;

     FOR n IN 1..20 LOOP
     increment := (increment * ((exponent + 1) - n) * x)/n;
     accumulator := accumulator + increment;
     END LOOP;
     RETURN accumulator;
     END pow2;"

--
Posted via http://dbforums.com

Received on Wed Sep 04 2002 - 20:09:36 CDT