Tweet
So I understand that GLOBAL DETERMINISTIC means that any execution of the SQL proc/function in any job, over any time, is expected to return the same results for the same inputs. So the SQL engine can cache results system wide for efficiency. So SQL Statement A in Job B called the function for input C, and produced result D, then if Statement F in job G called the function for input C, it could get cached result D without having to run the function. Very cool.
As opposed to STATEMENT DETERMINISTIC, which means that only executions within the same statement execution, are expected to return the same results for the same inputs. So the SQL engine will only cache within the statement execution. So if the same SQL statement in the same job was run twice in a row, the second execution could not used cached results form the first. You would use STATEMENT DETERMINISTIC if the results could change, but you always want the same result inside a single statement execution.
My question is, for GLOBAL, if/how/when does this global caching expire?
I have written a function that is based on a static control table of data, that in theory should never change. The function is marked STATEMENT DETERMINISTIC.
But theoretically, what if I made it GLOBAL DETERMINISTIC, and then for some reason the control table did change? How long would it be before any no-longer-inaccurate cached results were discarded and correct results returned?
As opposed to STATEMENT DETERMINISTIC, which means that only executions within the same statement execution, are expected to return the same results for the same inputs. So the SQL engine will only cache within the statement execution. So if the same SQL statement in the same job was run twice in a row, the second execution could not used cached results form the first. You would use STATEMENT DETERMINISTIC if the results could change, but you always want the same result inside a single statement execution.
My question is, for GLOBAL, if/how/when does this global caching expire?
I have written a function that is based on a static control table of data, that in theory should never change. The function is marked STATEMENT DETERMINISTIC.
But theoretically, what if I made it GLOBAL DETERMINISTIC, and then for some reason the control table did change? How long would it be before any no-longer-inaccurate cached results were discarded and correct results returned?
Comment