Re: Calling function from select statement is slower then using subselect in select

On Oct 25, 2006 at 5:54am Tomeo wrote:
(Snipped detail)

T> Why when I use function instead subquery, my query takes so long? T> What I'm doing wrong?

The less facetious answer is that you can see from the optimizer plans that by using the subquery, Oracle is able to optimize the entire operation as one entity, whereas by calling the function, Oracle can only optimize the original query in isolation, and the query in the function in isolation.

That's still a bit vague, but sounds convincing, right? I suspect Oracle is able to judge in the second case how worthwhile it will be to use indexes, for example, given the number of rows being returned, whereas using a function Oracle may not have that information. Also, the subquery may effectively be executed once, with associated sort time, etc, and then the appropriate results bought into the main query, whereas with the function it is effectively executed/sorted, etc, for each row of the main query.

*continue to wave hands wildly*

Regards, Steve

-- 
Steven Patterson, MSci (Hons), Oracle Certified Professional