MySQL Forums
Forum List  »  Stored Procedures

deterministic vs. non-deterministic
Posted by: Syam Gadde
Date: February 09, 2011 11:07AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
deterministic vs. non-deterministic
56957
February 09, 2011 11:07AM
11311
February 09, 2011 11:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.