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_nd as 
  hit_cnt        number := 0; 
  sign_threshold number := 50; 
end; 

create or replace function test_cond_nd (p_num number) return number as 
  l_ret number; 
begin 
  test_pack_nd.hit_cnt := test_pack_nd.hit_cnt + 1; 
  l_ret := p_num; 
  if test_pack_nd.hit_cnt > test_pack_nd.sign_threshold then  
    l_ret := -p_num; 
  end if; 
     
  return l_ret; 
end; 

create or replace procedure test_proc_nd (p_rows number, p_x number := 1, p_y number := 5) as 
  l_res number := 0; 
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_nd('||p_rows||', '||p_x||', '||p_y||') Function-Calls -------------'); 
  test_pack_nd.hit_cnt := 0; 
  l_res             := 0; 
  for c in (select * from test_tab where x = p_x and p_y = test_cond_nd(y)) loop 
    l_res := l_res + 1; 
  end loop; 
  dbms_output.put_line('Direct   FuncCall Count = '||test_pack_nd.hit_cnt||', Found Rows# = '||l_res); 
   
  test_pack_nd.hit_cnt := 0; 
  l_res             := 0; 
  for c in (select * from test_tab where x = p_x and (p_y = (select test_cond_nd(y) from dual))) loop 
    l_res := l_res + 1; 
  end loop; 
  dbms_output.put_line('InDirect FuncCall Count = '||test_pack_nd.hit_cnt||', Found Rows# = '||l_res); 
   
  test_pack_nd.hit_cnt := 0; 
  l_res             := 0; 
  for c in  
     (with sq as (select /*+ materialize */ * from test_tab where x = p_x order by y)  
      select * from sq where (p_y = (select test_cond_nd(y) from dual))) loop 
    l_res := l_res + 1; 
  end loop; 
  dbms_output.put_line('InDirectOrdered FuncCall Count = '||test_pack_nd.hit_cnt||', Found Rows# = '||l_res); 
end; 

exec test_proc_nd(1000, 1, 5)

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