Home » SQL & PL/SQL » SQL & PL/SQL » Does Oracle optimize function calls in SELECT stmt?
Does Oracle optimize function calls in SELECT stmt? [message #421628] Wed, 09 September 2009 10:05 Go to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
Hello everybody...

If I have, for example:

SELECT DECODE(fn_myfunction(), 0, 'N', 'Y'), fn_myfunction()
FROM table


That is, the same function is called twice or more times in the SELECT fields.

Is Oracle smart enough to run the function only once and to use the function result wherever it is called in the query?

Thanks
Jaime
Re: Does Oracle optimize function calls in SELECT stmt? [message #421632 is a reply to message #421628] Wed, 09 September 2009 10:10 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the function is determanistic and oracle knows that then it should only run it once, otherwise no.
Re: Does Oracle optimize function calls in SELECT stmt? [message #421634 is a reply to message #421628] Wed, 09 September 2009 10:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't tell it the function is deterministic and if the code is complex how could Oracle knows the function will return the same value each time?
So tell it it is DETERMINISTIC (if it is).

For instance:
SQL> select  dbms_random.value, dbms_random.value from emp;
     VALUE      VALUE
---------- ----------
.170656083 .044579736
.549259523 .435529577
.503842305 .114019288
.285816137 .939760469
.983279067 .974554588
.351890346   .1963539
.365413167 .505712967
.068610232 .490020812
.481136351 .766631305
.840565428 .138643163
.761380468 .926384859
.006594415 .917587579
.010927688 .658250226
.911780854 .717911623

14 rows selected.

Calling the function each time is valid.

Regards
Michel
Re: Does Oracle optimize function calls in SELECT stmt? [message #421637 is a reply to message #421628] Wed, 09 September 2009 10:18 Go to previous messageGo to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
Thanks for your replies.

Actually, my function will always return the same value if the same parameter is passed. It is sufficient to implement the function using

FUNCTION fn_myfunction(p_param IN NUMBER) RETURN NUMBER 
DETERMINISTIC IS
BEGIN
......
END;


Thanks
Jaime
Re: Does Oracle optimize function calls in SELECT stmt? [message #421639 is a reply to message #421637] Wed, 09 September 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you try it and post the result as I did?

Regards
Michel
Re: Does Oracle optimize function calls in SELECT stmt? [message #421656 is a reply to message #421628] Wed, 09 September 2009 10:52 Go to previous messageGo to next message
Jaime Stuardo
Messages: 57
Registered: March 2004
Member
I did it, but I've got problems.

First, I could add DETERMINISTIC keyword in package definition, but I could not in the package body. Compiler said that is not a valid option for subprogram.

Finally, I added it only in definition and I did the test but the function was called twice.

That function returns the result of a query. May this be the problem? perhaps only functions that uses only constant data can be used in deterministic functions as a web page telling about DETERMINISTIC keyword suggested:

Quote:

Description: Deterministic functions do not reference tables and always return the same result, based upon input, every time they are called.



So... is not there a way to specify Oracle that the result of a query will always return the same result? this is typical, for example, when querying master tables.

Thanks
Jaime
Re: Does Oracle optimize function calls in SELECT stmt? [message #421661 is a reply to message #421628] Wed, 09 September 2009 11:11 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Any function with a query in it is not deterministic by definition.
A with clause might help.
Or you could just call your function before the query and use a variable containing the result instead.
Re: Does Oracle optimize function calls in SELECT stmt? [message #421662 is a reply to message #421656] Wed, 09 September 2009 11:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend you to buy and read Stephane Faroult's book there is a section on how to handle this case.

Regards
Michel
Re: Does Oracle optimize function calls in SELECT stmt? [message #421663 is a reply to message #421656] Wed, 09 September 2009 11:21 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I think this is one of those times you need to rethink how you are approaching the problem. Do you really absolutely need the function? If so, is it really killing your performance to have to call it twice? More importantly, why would you ever need to call it twice? You could always do:
SELECT DECODE(fn, 0, 'N', 'Y'), fn
FROM (SELECT fn_myfunction() fn
      FROM table)

One call, same results.
Previous Topic: Table colunms
Next Topic: How to convert Long to Varchar2 ?
Goto Forum:
  


Current Time: Sat Dec 03 12:28:32 CST 2016

Total time taken to generate the page: 0.13790 seconds