Re: DETERMINISTIC Function in Pkg

Maxim Demenko schrieb:
> Michel Cadot schrieb:
>

>>
>> And if you want a headache:

Some more headache ;-)

SQL> set serveroutput on
SQL> create or replace package counter

   2 is
   3 val number := 0;
   4 end;
   5 /

Package created.

SQL> create or replace function f(i number)

   2 return number
   3 deterministic
   4 is
   5 begin
   6 counter.val:=counter.val+1;
   7 return trunc(dbms_random.value(i,2*i));    8 end;
   9 /

Function created.

SQL> create table t

   2 as
   3 select trunc(dbms_random.value(1,26)) id    4 from dual connect by level <=100000;

Table created.

SQL> create table t_new

   2 as
   3 select id
   4 from t where f(id)=id;

Table created.

SQL> exec dbms_output.put_line(counter.val); 25

PL/SQL procedure successfully completed.

SQL> select count(unique id) from t;

COUNT(UNIQUEID)

              25

SQL> -- Reset the environment
SQL> drop table t;

Table dropped.

SQL> drop table t_new;

Table dropped.

SQL> exec counter.val:=0;

PL/SQL procedure successfully completed.

SQL> create table t

   2 as
   3 select trunc(dbms_random.value(1,501)) id    4 from dual connect by level <=100000;

Table created.

SQL> create table t_new

   2 as
   3 select id
   4 from t where f(id)=id;

Table created.

SQL> exec dbms_output.put_line(counter.val); 21990

PL/SQL procedure successfully completed.

SQL> select count(unique id) from t;

COUNT(UNIQUEID)

             500

As i would interpret it, Oracle maintain certain structure to remember already calculated values ( along with arguments - probed rows ), that structure is obviously limited in size, but it would be interesting to know, how this structure is populated/aged out.

Best regards

Maxim Received on Thu Oct 04 2007 - 15:39:45 CDT