RE: Determination of "deterministic"
- From: Yong Huang <yong321@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>, John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
- Date: Sat, 30 Oct 2010 10:14:25 -0700 (PDT)
It looks like software-verified determinism is a difficult task. Computer
science studies deterministic algorithm and deterministic automaton. One
thing they attempt to do is the same as what I need except I don't care
about the intermediate steps. In terms of Oracle, I don't care, e.g.,
whether the execution plan changes, as long as the final result is the
same. Oracle's "deterministic" keyword is "honor system"-based on the
part of the function creator; it has the same psychological effect as
"Enter your email again" on many account sign-up web pages. It's only
*syntactically* required for function based indexes and materialized
views. The following throws an error:
create table t (x number);
create or replace function f(inp number) return date
--deterministic
as
d date;
begin
select sysdate into d from dual;
return d;
end;
/
SQL> create index t_fbi on t (f(x));
create index t_fbi on t (f(x))
*
ERROR at line 1:
ORA-30553: The function is not deterministic
But the error is gone if you uncomment the line in the silly function,
which a human knows is truly non-deterministic.
I opened an SR titled "Wrong result on first parse". Since it's highly
reproducible, I sent the schema dump to Oracle and they found "MAX(...)
OVER(ORDER BY ROWNUM)" in one of the views, which I had missed.
Rewriting it to not using rownum solved the problem. The case is
interesting in that the wrong result only occurs on first parse, after
shared pool aging or flushing or DDL on one of the tables. The second
execution always re-parses and always generates a different plan and the
result is correct from then on. In this case, even theoretically
unlimited resources may not be enough to prove determinism or lack of,
unless "resource" includes not just data but execution plan as well.
Yong Huang
--- On Sat, 10/30/10, John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx> wrote:
> Co-incidentally we had Julian Dyke on
> site today giving a seminar about execution plans and he
> made the same point about the deterministic keyword not
> actually performing any verification. Ric obviously makes a
> valid point that you would need unlimited resources to be
> able to correctly determine whether a function was
> deterministic or not but it does make you wonder what the
> 'function' of the keyword is, perhaps it is only to be used
> in place of a comment.
>
> John
>
> www.jhdba.wordpress.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
