> > Is there a way for Oracle, or software in general, to determine whether > a query is really deterministic, not by checking presence of certain > keywords, but by some other means? > Technically speaking a query is never "deterministic" (in the sense that this word is used for functions, I've never seen it being used for queries...), since queries are based on tables, and tables potentially get changed by transactions executed by other sessions, which in turn could influence the outcome of the query. On Fri, Oct 29, 2010 at 9:24 PM, Yong Huang <yong321@xxxxxxxxx> wrote: > A function can be marked as deterministic if you know the function always > returns the same set of data when you pass the same values to its > arguments (if any). If a function has certain keywords such as rownum > or sysdate, it won't be deterministic even if you use the keyword > "deterministic" in its definition: > > SQL> create or replace function f return date > 2 deterministic > 3 as > 4 d date; > 5 begin > 6 select sysdate into d from dual; > 7 return d; > 8 end; > 9 / > > Function created. > > SQL> select f from dual; > > F > ----------------- > 20101028 22:27:07 > > SQL> select f from dual; > > F > ----------------- > 20101028 22:27:08 > > Thus, presence of "deterministic" is NOT related to the true meaning of > the word "deterministic". > > On the other hand, this query > select * from (select * from t order by id) where rownum <= 10; > is definitely deterministic from the user's perspective if id is unique, > even though it has the word "rownum". > > Is there a way for Oracle, or software in general, to determine whether > a query is really deterministic, not by checking presence of certain > keywords, but by some other means? > > Yong Huang > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Toon Koppelaars RuleGen BV Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.RuleGen.com/pls/apex/f?p=14265:13