deterministic vs. non-deterministic

[Syam Gadde]

Hi,

The use of the DETERMINISTIC keyword in the stored function documentation reads:

> A routine is considered “deterministic” if it always produces the same result
> for the same input parameters, and “not deterministic” otherwise.

My question is whether tables (external to the function) that are referred to in the function are considered "inputs"? For example, I have defined a function that does not modify any data, but uses SELECT on several tables. If given the same input parameters and the same database state, the function will return the same value. However, if the data in the referred tables change in some way, the output may be different. Would I be OK specifying DETERMINISTIC here?

The problem is that a query of the form:

SELECT * FROM TableA WHERE TableA.id=myfunc(11, 34);

is much, much slower than:

SELECT TableA.* FROM TableA, (SELECT myfunc(11,34)) AS tempid WHERE TableA.id=tempid;

if the function "myfunc" is labeled "NOT DETERMINISTIC". But for all intents and purposes the function is deterministic for the duration of the query; am I going to run into problems declaring the function DETERMINISTIC?

Thanks for your help!



Edited 1 time(s). Last edit at 02/09/2011 11:08AM by Syam Gadde.