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

Tomeo wrote:
> Hi, I have SQL query, that I'm trying to tune up.
>
> If I simplify it, I can say: When I run my select statement, where I'm
> calling function
> (that is just returning ABS(SUM) value) in main query, then it takes
> much more longer, than if I replace function with subselect(subquery).
> Why this happens? I'm expecting almost the same execution time. Please
> see query, execution time, explain plan and statistic below.
>
> (snip)
>
> Why when I use function instead subquery, my query takes so long?
> What I'm doing wrong?
>

Why do you use "Optimizer=RULE" ???

Anyway ... if you use a function, the optimizer will have not the tinyest clue as to what this function does, so your function will just be executed once for each result row.
If you C&P your function-select into the big select, then the sql engine can take a look at the whole picture and (it seems in this case) come up with a much more performant plan.

br,
Martin Received on Wed Oct 25 2006 - 08:50:21 CDT