Re: Efficiency of Procedures/Functions Over In-line Code ?

A copy of this was sent to Michael Rothwell <michael_rothwell_at_non-hp-usa-om46.om.hp.com> (if that email address didn't require changing) On Thu, 31 Dec 1998 09:57:02 -0800, you wrote:

>Thomas Kyte wrote:
>>

 clip
>>
>> I find calling pl/sql from SQL to be useful in typically 3 cases:
>>
>> 1.) I want to create a parameterized view. I will create a view that looks
>> something like:
>>
>> create or replace view my_view
>> as
>> select * from some_table
>> where some_column = ( select MY_FUNCTION from dual );
>>
>> What we did in this case was to make it such that MY_FUNCTION gets called ONCE
>> per query instead of one per row per query. This is very fast and you don't
>> even realize that you are calling pl/sql from sql.
>>
>clip
>
>Thomas,
>
>Could you go into this a little more. I'm not quite
>following what you are doing in this case.
>
>Michael.

Sure, an example of creating a parameterized view can be read at: http://www.dejanews.com/getdoc.xp?AN=363868651

This link has a complex example of this at work: http://www.dejanews.com/getdoc.xp?AN=373620126

The reason this can be important (parameterizing views) is typically an optimizer issue. We would like predicates to be pushed way down into views, (evaluate the where clause ASAP so we work on as few rows as possible over time). In many cases the optimizer can do it for us, in others -- because the query is really complex or because it would semantically change the question, predicates cannot be 'pushed down' into a view. In these cases, we can build the views to be 'parameterized' so that the where clause is evaluated way down inside the original view definition.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.