Re: Function Deterministic "Enough"?

> Deterministic Function Question:
>
> We have several functions that query database tables (based on a
> parameter) to return a value. These functions are used in Where
> clauses, and execute sometimes thousands of times for each different
> parameter. Very wasteful. I understand Deterministic functions would
> execute only once.

Your understanding is wrong.

create or replace function not_called_once   (p in number)
  return int
  deterministic
as
begin
  sys.dbms_lock.sleep(2);
  return 2*p;
end not_called_once;
/

SQL> set timing on
SQL> select not_called_once(4) from dual;

NOT_CALLED_ONCE(4)

                 8

Elapsed: 00:00:02.10
SQL> select not_called_once(4) from dual;

NOT_CALLED_ONCE(4)

                 8

Elapsed: 00:00:02.05

The function is called twice, although being deterministic and marked as such.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch