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

Thank you very very much!
Tomas

On Oct 26, 7:34 am, Steven Patterson <S.R.Patter..._at_soton.ac.uk> wrote:
> 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
Received on Thu Oct 26 2006 - 03:39:23 CDT