RE: Determination of "deterministic"
Date: Fri, 29 Oct 2010 14:52:25 -0500
Message-ID: <C970F08BBE1E164AA8063E01502A71CF018F2798_at_WIN02.hotsos.com>
Nope.
Of course it could send in every possible value to the function at least twice and verify that it gets the same results every time...
Maybe when we have Star Trek type computers they will be able to do that.
Ric Van Dyke
Hotsos Enterprises
Hotsos Symposium
March 6 - 10, 2011
You have to be there, yea I'm talking to you.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
Sent: Friday, October 29, 2010 3:24 PM
To: oracle-l_at_freelists.org
Subject: Determination of "deterministic"
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
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Oct 29 2010 - 14:52:25 CDT