Re: A view with a user defined function

C Chang <cschang_at_maxinter.net> wrote in message news:<3DDDC475.7915_at_maxinter.net>...
> In general, we can create a view which use some of default functions
> such as DECODE or SUM to manipulate some columns. But Is it good
> practice to include my own function a view as follow:
>
> CREATE OR REPLACE VIEW FULL_VIEW AS
> SELECT h.rowid poh_rowid, h.po_id,
> h.po_date,l.buyer_id, l.order_id, myFunc(l.order_id)
> FROM po_heads h, po_lines l
> WHERE h.po_id = l.po_id;
>
> so that the query using the view can get extra record from a third
> table? I have tried today, it seem slow down my query. Need some
> suggestion ?
>
> C CHang

Rewrite the view as a join of 3 tables, or try to use inline views. The sql in your function is handled as recursive sql (so a second step), and most likely it is called for every record in your query. Also the optimizer definitely won't merge the 2 sql statements into 1 and optimize this one statement.
Generally: avoid embedding functions performing selects in other selects like hell.
Your strategy is NOT going to work, EVER!

Regards

Sybrand Bakker
Senior Oracle DBA Received on Fri Nov 22 2002 - 07:56:48 CST