A reader, March 11, 2016 - 10:48 am UTC
Thanks Connor for your answer.
This function is there in my system since 2009 and is widely used, so I'm not planning to make any changes in there. But then I made it Deterministic which helped us to improve performance.
My only concern is whether the value returns from this function is always correct or not. I did my share of testing and looks like it's giving correct answer. But there are so many scenarios to test.
By looking at function, what's you opinion ?
Also, How long does Oracle keep the value of a Deterministic function and reuse it ? Is it Session specific, or Time specific, or some thing else ?
Thanks.
March 11, 2016 - 10:47 pm UTC
Yes, I would say it is deterministic.
Deterministic results are cached in memory, and so may be aged out under memory pressure etc. And similarly, if you call the function with millions of different values, then its unlikely that all of them will remain cached.