Re: 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

"roobaron" <member_at_dbforums.com> wrote in message news:1776187.1031188176_at_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 - 22:20:50 CDT