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.

User defined functions are one of the main killers of performance, they can probably make a more elegant and flexible solution sometimes but performance almost always are worse because there must be context switch from sql between pl/sql engines. If you are doing this switch for thousands of rows it adds up and takes a very observable time. So use user defined functions very cautiously and when you really need them.
More info
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:60122715103602

Gints Plivna
http://www.gplivna.eu Received on Wed Oct 25 2006 - 13:59:00 CDT