Re: PL/SQL functions in SQL where clauses

Hi

User-written functions in WHERE clauses can murder performance, ESPECIALLY when they access tables. I have also written table-accessing functions to use in WHERE clauses, but I use packages to pre-load PL/SQL tables once for the whole session and then have the function return me a value from the PL/SQL table. The performance improvement is staggering.

You can also tune your function's query - why have an EXISTS clause? Why not select count(*) from base table rather than dual, then either RETURN LEAST(v_permission_p,1) or IF v_permission_p >= 1 THEN RETURN 1; ELSE RETURN 0; END IF; You'll probably find it's the function's SQL that is letting you down. Have you run the original statement (the one with the function-calling WHERE clause) through SQL_TRACE and tkprof? I suggest you do to see where the bottleneck is.

Another option is to have the function being selected in an in-line view, give it an alias and then reference the alias in the where clause.

Whatever you do, it will be iterative, trial and error, whatever you want to call it...

Good luck

Adrian Received on Fri Feb 15 2002 - 09:51:29 CET