Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Why functions in Select Clause affect performance?
The Explain Plan did not show any of the tables used in the function,
as expected. The SQL frankly is too complex to show here. What I
tried to find out was if there was any possibility that Oracle will
invoke functions in the Select Clause midway instead of at the last
moment.
After further tests, even the 20 seconds results may be misleading as SQL Plus may be applying the /*+ first_row /* to it. So I changed the sql to do something like this
create table testtbl as
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 ...
)
This also took a long time. I didn't wait for it to end as it clearly showed the function is where the problem is. So may be Oracle is doing what I expected: invoke the functions at the last moment. Received on Tue Apr 04 2006 - 18:25:28 CDT