drop table test_stat

create table test_stat
 (test_name      varchar2 (40),
   run_id         number,
   cnt            number,
   disticnt_cnt   number,
   elapsed        number,
   total_comp     number,
   single_comp    number)

create or replace package helper as
 p_num1_cnt number := 0;
 procedure format(p_name varchar2, p_value number);
 procedure record(p_test_name varchar2, p_cnt number, p_disticnt_cnt number, 
                  p_elapsed number, p_total_comp number, p_single_comp number);
end helper;

create or replace package body helper as
 procedure format(p_name varchar2, p_value number) as
 begin
  dbms_output.put_line(rpad(p_name, 40) || lpad(p_value, 10));
 end format;

 procedure record(p_test_name varchar2, p_cnt number, p_disticnt_cnt number, 
                  p_elapsed number, p_total_comp number, p_single_comp number) as
   l_run_id  number;
 begin
   select nvl(max(run_id), 0) + 1 into l_run_id from test_stat where test_name = p_test_name;
   insert into test_stat values(p_test_name, l_run_id, p_cnt, p_disticnt_cnt
                 ,p_elapsed, p_total_comp, p_single_comp);
   commit;
 end record; 
end helper;

drop type tobj_tab force

drop type tobj force

create or replace type tobj as object (
 p_num1 number,
 p_num2 number,
 map member function comp return integer);

create or replace type body tobj as
 map member function comp return integer is 
    begin 
      helper.p_num1_cnt := helper.p_num1_cnt + 1;
      return p_num2; 
    end; 
end;

create or replace type tobj_tab as table of tobj;

create or replace procedure set_test (p_name varchar2, p_size number default 1000
                   ,p_dist_val pls_integer := null) as
 l_dist_val     number := coalesce(p_dist_val, p_size);
  l_tobj_tab          tobj_tab := tobj_tab();
  l_tobj_tab_distinct tobj_tab := tobj_tab();
  l_start_time     number;
  l_elapsed        number;
begin
  select cast(collect(tobj(level, mod(level, l_dist_val))) as tobj_tab) into l_tobj_tab 
    from dual connect by level <= p_size; 
    
  helper.p_num1_cnt := 0;
  l_start_time := dbms_utility.get_time;
  l_tobj_tab_distinct := set(l_tobj_tab);
  l_elapsed := dbms_utility.get_time - l_start_time;
  
  -- SET test depends on number of objects, and also number of distinct objects
  helper.record(p_name, cardinality(l_tobj_tab), l_tobj_tab_distinct.count,
         l_elapsed, helper.p_num1_cnt, (helper.p_num1_cnt/p_size));
  
  helper.format(rpad('-', 40, '-'), null);
  helper.format('l_tobj_tab.count=', cardinality(l_tobj_tab));
  helper.format('l_tobj_tab_distinct.count=', l_tobj_tab_distinct.count);
  helper.format('elapsed(centi)=', l_elapsed);
  helper.format('Total Number of Object Comparisons=', helper.p_num1_cnt);
  helper.format('Number of Object Comparisons / Object=', (helper.p_num1_cnt/p_size));
  helper.format('l_dist_val=', l_dist_val);
end;

create or replace procedure set_test_run(p_run number) as
begin
 for i in 1..p_run loop
  set_test('SET_TEST', power(10, i), power(10, i));
 end loop;
 
 for i in 1..p_run loop
  set_test('SET_TEST_DIST', power(10, p_run), power(10, i));
 end loop;
end;

create or replace procedure equal_test (p_name varchar2, p_size number default 1000
             ,p_dist_val pls_integer := null, p_random boolean :=false) as
 l_dist_val  number := coalesce(p_dist_val, p_size);
 l_test_name   varchar2(40);
  l_tobj_tab_1  tobj_tab := tobj_tab();
  l_tobj_tab_2 tobj_tab := tobj_tab();
  l_start_time  number;
  l_elapsed     number;
begin
  select cast(collect(tobj(level, mod(level, l_dist_val))) as tobj_tab) into l_tobj_tab_1 
    from dual connect by level <= p_size; 
 
  if p_random then
   l_test_name := p_name ||'_RANDOM';
  select cast(collect(tobj(x, mod(x, l_dist_val))) as tobj_tab) into l_tobj_tab_2 
     from (select level x from dual connect by level <= p_size order by dbms_random.value);   
 else
  l_test_name := p_name;
  select cast(collect(tobj(x, mod(x, l_dist_val))) as tobj_tab) into l_tobj_tab_2 
     from (select level x from dual connect by level <= p_size);   
 end if;
    
  helper.p_num1_cnt := 0;
  l_start_time := dbms_utility.get_time;
  if (l_tobj_tab_1 = l_tobj_tab_2) then
   helper.format('l_tobj_tab_1 = l_tobj_tab_2', null);
  else 
   helper.format('l_tobj_tab_1 != l_tobj_tab_2', null);
  end if;
  l_elapsed := dbms_utility.get_time - l_start_time;
  
  helper.record(l_test_name, cardinality(l_tobj_tab_1), l_dist_val,
         l_elapsed, helper.p_num1_cnt, (helper.p_num1_cnt/p_size/2));
         
  helper.format('l_tobj_tab_1.count=', cardinality(l_tobj_tab_1));
  helper.format('l_tobj_tab_2.count=', l_tobj_tab_2.count);
  helper.format('elapsed(centi)=', l_elapsed);
  helper.format('Total Number of Object Comparisons=', helper.p_num1_cnt);
  helper.format('Number of Object Comparisons/Object=', (helper.p_num1_cnt/p_size/2));
  helper.format('l_dist_val=', l_dist_val);
end;

create or replace procedure equal_test_run(p_run number, p_random boolean :=false) as
begin
 for i in 1..p_run loop
  equal_test('EQUAL_TEST', power(10, i), power(10, i), p_random);
 end loop;
 
 for i in 1..p_run loop
  equal_test('EQUAL_TEST_DIST', power(10, p_run), power(10, i), p_random);
 end loop;
end;

create or replace procedure all_test_run_1(p_run number, p_random boolean :=false) as
begin
 set_test_run(p_run);
 equal_test_run(p_run, false);
 equal_test_run(p_run, true);
end;