Calling a procedure in the selection fields of a SELECT

[mrcomino]

I have so much queries similar to

select t1.c1,t1.c2,...,
t2.c1,..., procedure_very_big(t1.c1,t4,c2) ,t4.c4
from t1 ,t2, t3, t4
where ...;

rrrrr data rrrrrrr
rrrrr data rrrrrr
rrrrr data rrrrrr
3 rows selected

The execution takes several minutes,even hours.
And my question is

Is the Oracle calling the procedure for all the rows
of the join , or it is only calculating for the 3 rows
of the result set.

Please quick answer
Thanks in advance

 

[MikeJones]

It would have to be a function rather than a Procedure, as you can't call procedures from SQL. However, Oracle is smart enough to only call the function for the rows it has to, in other words the rows returned, so in your example it would only get called three times.

HTH,

Mike.

 

[Yvessss]

Beware of this approach. Functionality wise its great
but performance wise, is very dangerous.

When you know exactly what to expect ex. 5 rows max ,then
It's OK, but if the numbers of rwos are big or unknown,
avoid this.

In one case I had, was a simple date function that was
executed for many return rows. It was, in our case, a
show stopper. We had to replace the function with a
standard join, it was down to fraction of second again.

Yvessss