Oracle Docu writes:
The MEMBER [OF] or NOT MEMBER [OF] condition tests whether or not an element is a member of a nested table, returning the result as a Boolean value.
It seems that order method of object type is used in MEMBER OF Function to make direct one-to-one object comparisons.
Therefore, we use order method to record the number of comparisons.
Note: Tested in Oracle 19c and 22c.
1. Test Setup
drop type t_obj_tab force;
drop type t_obj_varray force;
drop type t_obj force;
create or replace noneditionable package recorder as
p_cnt number := 0;
end;
/
create or replace noneditionable type t_obj as object (
p_num number,
order member function comp (c t_obj) return integer
);
/
create or replace noneditionable type body t_obj as
order member function comp (c t_obj) return integer is
begin
recorder.p_cnt := recorder.p_cnt + 1; -- count the number of calling
-- for debug
-- dbms_output.put_line('COMP Debug: p_cnt: (p_num > c.p_num) ? sign = '||recorder.p_cnt||
-- ' ('||p_num||' > '||c.p_num||' ?) = '||sign(p_num - c.p_num));
return sign(p_num - c.p_num); -- return 1, -1, 0 for bigger, less, equal
end;
end;
/
create or replace noneditionable type t_obj_tab as table of t_obj;
/
create or replace noneditionable type t_obj_varray as varray(100) of t_obj;
/
drop table test_tab;
create table test_tab (id number, obj_tab t_obj_tab, obj_varray t_obj_varray)
nested table obj_tab store as test_tab_obj_store
/
create or replace procedure fill_tab(p_rows number := 1000, p_obj_tab_size number := 100) as
l_obj_tab t_obj_tab;
l_obj_varray t_obj_varray;
begin
execute immediate'truncate table test_tab';
-- If inserting the same l_obj_tab (or same l_obj_varray), Oracle observes the same objects and optimizes (reduce) to one for the same objects.
--select cast(collect(t_obj(level)) as t_obj_tab) into l_obj_tab from dual connect by level <= l_obj_tab_size;
--select t_obj(level) bulk collect into l_obj_varray from dual connect by level <= l_obj_tab_size;
--insert into test_tab select level, l_obj_tab, l_obj_varray from dual connect by level <= l_rows;
-- Insert 1000 rows, each row has one l_obj_tab and one l_obj_varray with 100 different numbers (values are 1..100)
-- The first element is always 1, the others (2-100) are sorted by dbms_random.value.
-- For each row, insert different l_obj_tab/l_obj_varray by dbms_random.value.
dbms_random.seed(31); -- to make test stable
for i in 1..p_rows loop
with sq as (select level+1 id from dual connect by level <= (p_obj_tab_size -1) order by trunc(dbms_random.value(1, p_rows)))
select t_obj(id), t_obj(id) bulk collect into l_obj_tab, l_obj_varray
from (select 1 id from dual
union all
select * from sq);
insert into test_tab values (i, l_obj_tab, l_obj_varray);
end loop;
commit;
end;
/
-- exec fill_tab(1000, 100);
create or replace procedure memberof_test (p_size number := 100, p_sign number := 1, p_tab varchar2 := 'varray') as
l_obj_tab t_obj_tab := t_obj_tab();
l_obj_varray t_obj_varray := t_obj_varray();
l_start_time number;
l_elapsed number;
l_cnt number := 0;
begin
select cast(collect(t_obj(p_sign*level)) as t_obj_tab) into l_obj_tab from dual connect by level <= p_size order by level;
recorder.p_cnt := 0;
l_start_time := dbms_utility.get_time;
if p_tab = 'varray' then
for c in (
select * from test_tab p
where exists
(select 1 from table (p.obj_varray) t
where t_obj(t.p_num) member of cast (l_obj_tab as t_obj_tab))
) loop
l_cnt := l_cnt + 1;
end loop;
else
for c in (
select * from (select * from test_tab p order by p.id) p
where exists
(select 1 from table (p.obj_tab) t
where t_obj(t.p_num) member of cast (l_obj_tab as t_obj_tab))
-- in COMP method, "(c t_obj)" is "t_obj(t.p_num)"; (self t_obj) is from "l_obj_tab"
) loop
l_cnt := l_cnt + 1;
end loop;
end if;
l_elapsed := dbms_utility.get_time - l_start_time;
dbms_output.put_line('Selected Rows = ' || l_cnt);
dbms_output.put_line('Elapsed(centi) = ' || l_elapsed);
dbms_output.put_line('Comparisons = ' || recorder.p_cnt);
dbms_output.put_line('obj_tab.size = ' || cardinality(l_obj_tab));
end;
/
2. Test Run
At first, we fill test_tab with 1000 rows, each row has one nested table and one varray of 100 different numbers (values are 1..100).
The first element is always 1, the others (2-100) are sorted by dbms_random.value.
exec fill_tab(1000, 100);
With positive numbers, "MEMBER OF" returns true for first element.
With negative numbers, "MEMBER OF" returns false after going all elements.
exec memberof_test(10, +1, 'varray');
exec memberof_test(10, -1, 'varray');
exec memberof_test(100, +1, 'varray');
exec memberof_test(100, -1, 'varray');
exec memberof_test(10, +1, 'ntab');
exec memberof_test(10, -1, 'ntab');
exec memberof_test(100, +1, 'ntab');
exec memberof_test(100, -1, 'ntab');
3. Test Outcome
In case of varray, with positive numbers, the number of Comparisons is the same as table rows; with negative numbers, the number of Comparisons is ("table rows" * "varray size" * "number of positive numbers");
In case of nested table, with positive numbers, the number of Comparisons is more than table rows because nested table in each row is physically stored in "test_tab_obj_store" (nested table storage), and its physical sequence is not the same as its logical sequence (one can dump "test_tab_obj_store" data block to check them).
With negative numbers, the number of Comparisons is ("table rows" * "varray size" * "number of positive numbers");
SQL > exec memberof_test(10, +1, 'varray');
Selected Rows = 1000
Elapsed(centi) = 16
Comparisons = 1000
obj_tab.size = 10
SQL > exec memberof_test(10, -1, 'varray');
Selected Rows = 0
Elapsed(centi) = 325
Comparisons = 1000000
obj_tab.size = 10
SQL > exec memberof_test(100, +1, 'varray');
Selected Rows = 1000
Elapsed(centi) = 16
Comparisons = 1000
obj_tab.size = 100
SQL > exec memberof_test(100, -1, 'varray');
Selected Rows = 0
Elapsed(centi) = 3174
Comparisons = 10000000
obj_tab.size = 100
SQL > exec memberof_test(10, +1, 'ntab');
Selected Rows = 1000
Elapsed(centi) = 16
Comparisons = 4491
obj_tab.size = 10
SQL > exec memberof_test(10, -1, 'ntab');
Selected Rows = 0
Elapsed(centi) = 328
Comparisons = 1000000
obj_tab.size = 10
SQL > exec memberof_test(100, +1, 'ntab');
Selected Rows = 1000
Elapsed(centi) = 16
Comparisons = 2662
obj_tab.size = 100
SQL > exec memberof_test(100, -1, 'ntab');
Selected Rows = 0
Elapsed(centi) = 3174
Comparisons = 10000000
obj_tab.size = 100
