Re: function call from a view slows things down ?

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.

Hth,

Sybrand Bakker, Oracle DBA

<jeanch_at_my-deja.com> schreef in berichtnieuws 8hqomk$qcm$1_at_nnrp1.deja.com...
> Folks,
>
> I was wondering if any of you had an idea why this
> view will run slower than normal view.
> o As you've noticed, a function call from the view
> is operated and the the data type passed to getData
> function is VARCHAR2 and the col1 datatype on the
> table is a number; basically I do some data conversion
> from number to formatted string process before printing data.
>
> o likewise I use a trigger on the view to allow ne the
> conversion back from varchar2 to number before inserting
> into the table; that as well is a slow process.
>
>
>
> CREATE OR REPLACE VIEW myview ( col1, col2)
> AS SELECT scott.mypkg.getData(tab.col1, tab.col2) col1,
> tab.col2 SIGNALING_POINT_NAME
> FROM mytab tab
>
> Cheers
> JC
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Jun 09 2000 - 00:00:00 CDT