I've read from many sources about deterministic functions in Oracle and it is clear to me. But there is a detail which I can't find the answer.
I have a view that is using a function on 4 places. Because of many rows the function is called 4 times/row and this takes time. Ok, thats logical. The function is simple:
- it returns sysdate or trunc(sysdate), depending on the parameter;
- or if there is a row in the parameter table, it returns that date from a table. So I can have a user defined "sysdate".
So in theory, the function is not deterministic, because it depends on the row in the table and also sysdate is almost every time different. But, for the time that is needed to return all rows from a view, the function in a specific way can be deterministic. So I've put deterministic tag in the function and the view is returning rows much quicker, because funtion is called once or only a few times.
So my question is: when could database reuse the return value of the deterministic function? For sure during the execution of one concrete sql. But can we say the the function is executed at least once for each sql? Or if I ask in another way: is deterministic behaviour of the funtion expected only for a sql statememt, or also for the whole transaction, session or even between sessions? So can we be sure, that after one sql with the function, for the next usage of the function it will be re-run?
Thanks for answer.
Simon