PL/SQL function as part of a WHERE clause
If I have a statement that calls a PL/SQL function as part of the where clause...
If I have a statement that calls a PL/SQL function as part of the where clause...
SELECT *
FROM my_table
WHERE value = my_package.my_func('constant');
It appears to call the function for every row in my_table thus rendering the index on value useless and resulting in poor performance. Is there a way I can get Oracle to understand that my_package.my_func('constant') will always return the same value and therefore only call it once? One option I have considered is calling the function outside the SQL and passing the return value in. This works but in this particular case that option is not ideal. So is there another way?
One option that I have done is to call this function and store the value in a variable. Then use bind variables in your WHERE clause and if the Optimizer decides it is best, it will use the index on the VALUE column.
The exact details on how to do this depend on your application development platform.
