RE: Determination of "deterministic"

From: Yong Huang <yong321_at_yahoo.com>
Date: Sat, 30 Oct 2010 10:14:25 -0700 (PDT)
Message-ID: <513235.51303.qm_at_web80606.mail.mud.yahoo.com>



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_at_morrisonsplc.co.uk> 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
      

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Oct 30 2010 - 12:14:25 CDT

Original text of this message