deterministic test

deterministic_test.sql

create or replace 
function get_ref_name(
  num_n_ref_id                        si_ref.n_ref_id%type,
  num_n_lang_id                       si_ref.n_lang_id%type := sys_context('MAIN', 'N_LANG_ID')
)
return si_ref.vc_name%type
is
  vch_vc_name                         si_ref.vc_name%type;
begin
  select nvl(l.vc_name, r.vc_name)
  into   vch_vc_name
  from   si_ref r,
         li_ref l
  where  r.n_ref_id = num_n_ref_id
  and    r.c_active = 'Y'
  and    l.n_ref_id  (+) = r.n_ref_id
  and    l.n_ref_id  (+) = num_n_ref_id
  and    l.n_lang_id (+) = num_n_lang_id;
  
  return vch_vc_name;
end get_ref_name;
/

create or replace 
function get_ref_name_static(
  num_n_ref_id                        si_ref.n_ref_id%type,
  num_n_lang_id                       si_ref.n_lang_id%type := sys_context('MAIN', 'N_LANG_ID')
)
return si_ref.vc_name%type
deterministic
is
  vch_vc_name                         si_ref.vc_name%type;
begin
  select nvl(l.vc_name, r.vc_name)
  into   vch_vc_name
  from   si_ref r,
         li_ref l
  where  r.n_ref_id = num_n_ref_id
  and    r.c_active = 'Y'
  and    l.n_ref_id  (+) = r.n_ref_id
  and    l.n_ref_id  (+) = num_n_ref_id
  and    l.n_lang_id (+) = num_n_lang_id;
  
  return vch_vc_name;
end get_ref_name_static;
/

declare
  vch_vc_name                         si_ref.vc_name%type;
  tbl_strings                         varchar_short_table := varchar_short_table(100000);
begin
  -- get_ref_name - 3.412 s
  -- get_ref_name_static - 0.124 s
  select get_ref_name_static(3009)
  bulk collect into tbl_strings
  from   dual
  connect by level <= 100000;
end;
/