Re: Performance of User defined functions in Select stmts

>I am in the process of replacing a multi layer DECODE statement with a call
>to a function. I will be running on Oracle 7.1.6 for a short time and
the
>statement will need to process up to 8 million records.
>
>Using the decode method it currently takes 2 hours to run. Should I leave
>well alone?
>
>

It depends what your function is doing... and how smart you can make it. If you are calling it only a few times per row, and it is reasonably fast, it will probably be OK. But there are a few things to look out for...DO NOT USE the computed result in and ORDER BY clause or as part of the WHERE or GROUP by. Then every single row will have to be evaluated before it even starts doing anything.

Bottom line - if you can do it in a DECODE, that will probalby be faster than writing the equivalent in PL/SQL. However, if you can't, PL/SQL is reasonably quick.

Also, unless you have stumbled across it, you will probably need to learn a little about the PRAGMA statement in PL/SQL to allow you to actually use a function in a SQL statement.

Hope that helps
Dan Hekimian-Williams Received on Sun Aug 30 1998 - 01:04:54 CDT