Re: Determination of "deterministic"
- From: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- To: yong321@xxxxxxxxx
- Date: Sat, 30 Oct 2010 11:12:49 +0200
>
> 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
Other related posts:
