Re: Why functions in Select Clause affect performance?

Kin wrote:
> I have a SQL statement that uses a user defined function like this
>
> select f(a.a, b.b, c.c)
> from a, b, c
> where ...
>
> This statement took about 50 minutes (not seconds) to return the
> result.
>
> If I changed the SQL statment slightly like the following:
>
> select f(a_a,b_b,c_c)
> from
> (
> select a.a as a_a, b.b as b_b, c.c as c_c
> from a, b, c
> where ...
> )
>
> Then it will return results in 20 seconds.
>
> What puzzles me is it seems the functions in the Select clause are NOT
> always run against the final result set but some intermediate ones.
>
> Can someone explain the reasons why Oracle is doing that?
>
> Thanks

Without version number and Explain Plan it is impossible to tell what is actually happening.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)