Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: function call from a view slows things down ?

Re: function call from a view slows things down ?

From: Peter L <news_at_abc.co.uk>
Date: 2000/06/09
Message-ID: <394177b4.9381728@news.freeserve.net>#1/1

On Fri, 9 Jun 2000 16:30:27 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>A few remarks
>1 calling the function requires going from the sql-engine to the
>pl/sql-engine: a context switch
>2 the function needs to be validated by the pl/sql-engine
>3 going back to the sql-engine requires another context switch.
>
>Repeat this for every record in the view.
>If the function contains sql, this is handled as recursive sql, so a second
>layer of processing, the two sql-statements will not be merged into one.
>
>Step 2 can be avoided if the package is pinned in the shared pool by issuing
>exec dbms_shared_pool.keep('<package name>').
>
>But yes, it will be slower : unavoidable. If that's conversion is not too
>complex, take it out and run it directly.
>
>

Calling a function in a select may force the execution from parallel to serial. You can code the function with the 'parallel enable' hint (or something along those lines) which will allow each process to execute it's own copy of the function. Received on Fri Jun 09 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US