Determination of "deterministic"
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 29 Oct 2010 12:24:15 -0700 (PDT)
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
Other related posts:
