Problems with Power function 8.1.7.0 
Author Message
 Problems with Power function 8.1.7.0

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://www.***.com/



Mon, 21 Feb 2005 09:09:36 GMT
 Problems with Power function 8.1.7.0

I could only test this on 8.1.7.3 after assuming that the following
test case represents your problem:

SQL>select months_between(sysdate + rownum, sysdate),
             case when (months_between(sysdate + rownum, sysdate) > 12)
                     then 3 * power(1.05,months_between(sysdate + rownum, sysdate))
             end case
          from some_big_table;

MONTHS_BETWEEN(SYSDATE+ROWNUM,SYSDATE)       CASE
-------------------------------------- ----------
-------- snip a lot of rows -------
                            5923.58065 9.855E+125
                             5923.6129 9.870E+125
                            5923.64516 9.886E+125
                            5923.67742 9.901E+125
                            5923.70968 9.917E+125

180300 rows selected.

No problem ...
Oracle 8.1.7.3. 64 bit on Solaris.

Does the same test case fail in your case?
What makes you think that power function is the reason for the hang.
I'm assuming that the workaround worked which why you think the power is at fault?

Anurag

Quote:

> 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



Mon, 21 Feb 2005 11:20:50 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Problem with POWER function.

2. Power Builder: Modify function and Ampersand Problem

3. Power function precision issues

4. POWER Function causes domain error

5. Error in function Power

6. Power and sqrt functions

7. Calling a PL/SQL Function from Power Builder.

8. MOD function and the power of Oracle

9. power function is slow

10. Power() function Using Negative Exponents

11. MS SQL Server + Power Builder + Built-In Functions

12. Power and sqrt functions


 
Powered by phpBB® Forum Software