Why functions in Select Clause affect performance?

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 Received on Tue Apr 04 2006 - 13:17:23 CDT