Re: Using functions in procedure cursor

On 21 Mar 2002 10:39:24 -0800, g_rock99_at_hotmail.com (Greg) wrote:

>I am trying to use an Oracle stored procedure that takes an cursor
>parameter to be used with a SeaGate report. The procedure was working
>fine until I began using a custom function in the Select statement of
>the cursor. It looks something like this:
>
>
>CREATE OR REPLACE PROCEDURE TEST (cur_cbsus IN OUT
>S9028K01.CINFO_TYPE,
>whls_name IN CHAR, subm_str_dte IN DATE, subm_end_dte IN DATE)
>IS
>BEGIN
>
> OPEN cur_cbsus FOR
> SELECT ..., testpack.contract (to_char(buid.bunit_num), to_char
> (prodid.prod_num),submitem.submitem_dt_cbk_invoice)
> FROM ...;
>
>END;...
>
>
>The function is within a package "testpack". When I run the simple
>select from a SQL PLUS environment it comes pack in under 1 minute.
>However, it hadn't finished in over 2 hours in crystal, so I tried to
>run the procedure in an anonymous block and the same problem occured.
>The procedure works fine in both if I comment the function out. Why
>does the use of a function in a select statement within a
>cursor/procedure cause this? What can I do?
>
>Thanks,
>Greg

Functions used in this fashion are never optimised away. Most likely your function contains at least one select, so an implicit join. The optimizer however doesn't see this and treats your function as recursive sql. This means: recursive sql is executed for every individual row provided by the main query. Of course this is killing performance.
You should be capable to confirm what I describe easily by running an explain or tkprof on the affected query.

Being able to use home-grown functions has an important downside: usually a non-procedural solution is more efficient but also more difficult to conceive. In that case resorting to 3gl techniques is all too 'smart'/'easy'

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Mar 21 2002 - 13:47:27 CST