drop table test_tab

create table test_tab (x number, y number)

create index test_tab_ind_x on test_tab(x)

create or replace package test_pack as 
  hit_cnt number := 0; 
end; 

create or replace function test_cond (p_num number) return number as 
begin 
  test_pack.hit_cnt := test_pack.hit_cnt + 1; 
  return p_num; 
end; 

create or replace procedure test_proc (p_rows number, p_x number := 1, p_y number := 5) as 
begin 
  execute immediate 'truncate table test_tab'; 
  insert into test_tab select mod(level, 2) x, mod(level, 100) y from dual connect by level <=p_rows; 
  commit; 
  dbms_stats.gather_table_stats(null, 'TEST_TAB', cascade=>true); 
   
   dbms_output.put_line('---------- Compare test_proc('||p_rows||', '||p_x||', '||p_y||') Function-Calls -------------'); 
  test_pack.hit_cnt := 0; 
  for c in (select * from test_tab where x = p_x and p_y = test_cond(y)) loop 
    null; 
  end loop; 
  dbms_output.put_line('Direct   Count = '||test_pack.hit_cnt); 
   
  test_pack.hit_cnt := 0; 
  for c in (select * from test_tab where x = p_x and (p_y = (select test_cond(y) from dual))) loop 
    null; 
  end loop; 
  dbms_output.put_line('InDirect Count = '||test_pack.hit_cnt);  
end; 

exec test_proc(1000, 1, 5)

begin 
  test_proc(100, 0, 5); 
  test_proc(183, 0, 5); 
  test_proc(184, 0, 5); 
  test_proc(200, 0, 5); 
 
  test_proc(100, 1, 5); 
  test_proc(186, 1, 5); 
  test_proc(187, 1, 5); 
  test_proc(200, 1, 5); 
end;