Oracle · PL/SQL - SQL

Deterministic Functions

In oracle deterministic functions returns always the same value given the same inputs. If you have a function in a select statement that executes many times and consumes valuable CPU time It is a little bit hard to tune that query. In that case If you can it might be your best option to convert the function in a deterministic function. In that case oracle makes it optimization and makes the function call less than expected.

I the following examples I tried to show the execution numbers for a function that is create in both ways. The runs are made in 11.2.0.3 environment. You can calculate the execution times using dbms_output, using calling another pragma autonomous function but I was a little bit lazy. I calculate it from v$sql view.

–Script to Find execution count

select executions, s.sql_text
  from v$sql s
 where program_id  in
       (select object_id 
               from dba_objects 
             where object_name = 'MY_FUNC'
       );

–Create table

create table my_table_eser as select rownum rn, object_id  from dba_objects ;
create index my_table_eser_x1 on my_table_eser (rn);

NONDETERMINISTIC
–Create Classic Nondeterministic function

create or replace function my_func(v_num1 number) return number as
  v_num2 number;
begin
  select v_num1 into v_num2 from dual;
  return v_num2;
end;

–exec count : 1

select my_func(1) from dual ;

–exec count : Number of Objects

select my_func(1) from dba_objects ;

–exec count : Number of Objects (full table scan)

select count(*) from my_table_eser 
       where object_id = my_func(2);

–exec count : 1 (using index scan)

select count(*) from my_table_eser 
       where rn = my_func(2);

–exec count : 100

create or replace procedure my_func2
as v_sil number;
begin
  for i in 1..100 loop
    select my_func(1) into v_sil  from dual ;
  end loop ;
end;
/
begin 
  my_func2;
end;
/

DETERMINISTIC
–Create deterministic function

create or replace function my_func(v_num1 number) return number
  deterministic as
  v_num2 number;
begin
  select v_num1 into v_num2 from dual;
  return v_num2;
end;

–exec count : 1

select my_func(1) from dual ;

–exec count : 1

select my_func(1) from dba_objects ;

–exec count : 1 (full table scan)

select count(*) from my_table_eser 
       where object_id = my_func(2);

–exec count : 1 (using index scan)

select count(*) from my_table_eser 
       where rn = my_func(2);

–exec count : 100

create or replace procedure my_func2
as v_sil number;
begin
  for i in 1..100 loop
    select my_func(1) into v_sil  from dual ;
  end loop ;
end;
/
begin 
  my_func2;
end;
/

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s