RE: Determination of "deterministic"
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <yong321@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Oct 2010 14:52:25 -0500
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Yong Huang
Sent: Friday, October 29, 2010 3:24 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
