COLLECT
COLLECT is an aggregate function that takes as its argument a column of any type and creates a nested table of the input type out of the rows selected.
Multiset Operators
Multiset operators combine the results of two nested tables into a single nested table.
1. Setup the test (see appended Test Code)
2. Run the test by:
truncate table test_stat;
exec all_test_run_2(4);
3. Wait test finished, query the statistics by:
SET LINES 400
SET NUMWIDTH 12
SET NUMFORMAT 999,999,999
COLUMN TOTAL_COMP_EST_RATIO FORMAT 999.99
select s.*, round(s.total_comp_est/nullif(s.total_comp, 0), 2) total_comp_est_ratio
from (select t.*, round(10*1000*elapsed/nullif(total_comp, 0), 2) us_per_comp,
case test_name
when 'COLLECT_SORT' then round(cnt*ln(cnt)*3)
when 'COLLECT_SORT_DIST' then round(cnt*ln(cnt)*3/(greatest(log(10, cnt/disticnt_cnt), 1)))
when 'COLLECT_DISTINCT' then 2*cnt
when 'COLLECT_DISTINCT_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
when 'MULTISET_UNION_ALL' then 2*cnt
when 'MULTISET_UNION_ALL_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
when 'MULTISET_UNION_DISTINCT' then 2*cnt
when 'MULTISET_UNION_DISTINCT_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
when 'MULTISET_EXCEPT_ALL' then 2*cnt
when 'MULTISET_EXCEPT_ALL_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
when 'MULTISET_EXCEPT_DISTINCT' then 2*cnt
when 'MULTISET_EXCEPT_DISTINCT_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
when 'MULTISET_INTERSECT_ALL' then 2*cnt
when 'MULTISET_INTERSECT_ALL_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
when 'MULTISET_INTERSECT_DISTINCT' then 2*cnt
when 'MULTISET_INTERSECT_DISTINCT_DIST' then 2*cnt*(greatest(log(10, cnt/disticnt_cnt), 1))
end total_comp_est
from test_stat t) s;
returns:
TEST_NAME
RUN_ID
CNT
DISTICNT_CNT
ELAPSED
TOTAL_COMP
SINGLE_COMP
US_PER_COMP
TOTAL_COMP_EST
TOTAL_COMP_EST_RATIO
COLLECT_SORT
1
10
10
0
50
5
0
69
1.38
COLLECT_SORT
2
100
100
2
1278
12.78
15.65
1382
1.08
COLLECT_SORT
3
1000
1000
38
21396
21.396
17.76
20723
0.97
COLLECT_SORT
4
10000
10000
517
302050
30.205
17.12
276310
0.91
COLLECT_SORT_DIST
1
10000
10
117
59990
5.999
19.5
92103
1.54
COLLECT_SORT_DIST
2
10000
100
212
117108
11.7108
18.1
138155
1.18
COLLECT_SORT_DIST
3
10000
1000
322
184044
18.4044
17.5
276310
1.5
COLLECT_SORT_DIST
4
10000
10000
515
302050
30.205
17.05
276310
0.91
COLLECT_DISTINCT
1
10
10
0
26
2.6
0
20
0.77
COLLECT_DISTINCT
2
100
100
1
214
2.14
46.73
200
0.93
COLLECT_DISTINCT
3
1000
1000
5
2020
2.02
24.75
2000
0.99
COLLECT_DISTINCT
4
10000
10000
45
20028
2.0028
22.47
20000
1
COLLECT_DISTINCT_DIST
1
10000
10
105
57968
5.7968
18.11
60000
1.04
COLLECT_DISTINCT_DIST
2
10000
100
200
116242
11.6242
17.21
40000
0.34
COLLECT_DISTINCT_DIST
3
10000
1000
279
164002
16.4002
17.01
20000
0.12
COLLECT_DISTINCT_DIST
4
10000
10000
45
20028
2.0028
22.47
20000
1
MULTISET_UNION_ALL
1
10
10
0
26
2.6
0
20
0.77
MULTISET_UNION_ALL
2
100
100
0
214
2.14
0
200
0.93
MULTISET_UNION_ALL
3
1000
1000
5
2020
2.02
24.75
2000
0.99
MULTISET_UNION_ALL
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_UNION_ALL_DIST
1
10000
10
104
57968
5.7968
17.94
60000
1.04
MULTISET_UNION_ALL_DIST
2
10000
100
199
116242
11.6242
17.12
40000
0.34
MULTISET_UNION_ALL_DIST
3
10000
1000
280
164002
16.4002
17.07
20000
0.12
MULTISET_UNION_ALL_DIST
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_UNION_DISTINCT
1
10
10
0
26
2.6
0
20
0.77
MULTISET_UNION_DISTINCT
2
100
100
1
214
2.14
46.73
200
0.93
MULTISET_UNION_DISTINCT
3
1000
1000
4
2020
2.02
19.8
2000
0.99
MULTISET_UNION_DISTINCT
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_UNION_DISTINCT_DIST
1
10000
10
104
57968
5.7968
17.94
60000
1.04
MULTISET_UNION_DISTINCT_DIST
2
10000
100
199
116242
11.6242
17.12
40000
0.34
MULTISET_UNION_DISTINCT_DIST
3
10000
1000
279
164002
16.4002
17.01
20000
0.12
MULTISET_UNION_DISTINCT_DIST
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_EXCEPT_ALL
1
10
10
1
26
2.6
384.62
20
0.77
MULTISET_EXCEPT_ALL
2
100
100
0
214
2.14
0
200
0.93
MULTISET_EXCEPT_ALL
3
1000
1000
5
2020
2.02
24.75
2000
0.99
MULTISET_EXCEPT_ALL
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_EXCEPT_ALL_DIST
1
10000
10
104
57968
5.7968
17.94
60000
1.04
MULTISET_EXCEPT_ALL_DIST
2
10000
100
199
116242
11.6242
17.12
40000
0.34
MULTISET_EXCEPT_ALL_DIST
3
10000
1000
280
164002
16.4002
17.07
20000
0.12
MULTISET_EXCEPT_ALL_DIST
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_EXCEPT_DISTINCT
1
10
10
0
26
2.6
0
20
0.77
MULTISET_EXCEPT_DISTINCT
2
100
100
1
214
2.14
46.73
200
0.93
MULTISET_EXCEPT_DISTINCT
3
1000
1000
5
2020
2.02
24.75
2000
0.99
MULTISET_EXCEPT_DISTINCT
4
10000
10000
47
20028
2.0028
23.47
20000
1
MULTISET_EXCEPT_DISTINCT_DIST
1
10000
10
104
57968
5.7968
17.94
60000
1.04
MULTISET_EXCEPT_DISTINCT_DIST
2
10000
100
200
116242
11.6242
17.21
40000
0.34
MULTISET_EXCEPT_DISTINCT_DIST
3
10000
1000
280
164002
16.4002
17.07
20000
0.12
MULTISET_EXCEPT_DISTINCT_DIST
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_INTERSECT_ALL
1
10
10
1
26
2.6
384.62
20
0.77
MULTISET_INTERSECT_ALL
2
100
100
0
214
2.14
0
200
0.93
MULTISET_INTERSECT_ALL
3
1000
1000
5
2020
2.02
24.75
2000
0.99
MULTISET_INTERSECT_ALL
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_INTERSECT_ALL_DIST
1
10000
10
105
57968
5.7968
18.11
60000
1.04
MULTISET_INTERSECT_ALL_DIST
2
10000
100
200
116242
11.6242
17.21
40000
0.34
MULTISET_INTERSECT_ALL_DIST
3
10000
1000
281
164002
16.4002
17.13
20000
0.12
MULTISET_INTERSECT_ALL_DIST
4
10000
10000
47
20028
2.0028
23.47
20000
1
MULTISET_INTERSECT_DISTINCT
1
10
10
0
26
2.6
0
20
0.77
MULTISET_INTERSECT_DISTINCT
2
100
100
0
214
2.14
0
200
0.93
MULTISET_INTERSECT_DISTINCT
3
1000
1000
5
2020
2.02
24.75
2000
0.99
MULTISET_INTERSECT_DISTINCT
4
10000
10000
46
20028
2.0028
22.97
20000
1
MULTISET_INTERSECT_DISTINCT_DIST
1
10000
10
105
57968
5.7968
18.11
60000
1.04
MULTISET_INTERSECT_DISTINCT_DIST
2
10000
100
201
116242
11.6242
17.29
40000
0.34
MULTISET_INTERSECT_DISTINCT_DIST
3
10000
1000
280
164002
16.4002
17.07
20000
0.12
MULTISET_INTERSECT_DISTINCT_DIST
4
10000
10000
46
20028
2.0028
22.97
20000
1
Test Code
-- At first run Test Code in precedent Blog: "Performance of Oracle Object Collection Comparisons - Part1"
-- (http://ksun-oracle.blogspot.ch/2016/01/performance-of-oracle-object-collection_13.html)
------------------------------- COLLECT_test ---------------------------------
create or replace procedure collect_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_test_name varchar2(40);
l_tobj_tab_1 tobj_tab := tobj_tab();
l_start_time number;
l_elapsed number;
begin
helper.p_num1_cnt := 0;
l_start_time := dbms_utility.get_time;
if p_name in ('COLLECT_SORT', 'COLLECT_SORT_DIST') then
select cast(collect(tobj(num1, num2) order by tobj(num1, num2) desc) as tobj_tab) into l_tobj_tab_1
from (select level num1, mod(level, l_dist_val) num2 from dual connect by level <= p_size);
else
-- Workaround due to PL/SQL: ORA-30482: DISTINCT option not allowed for this function
-- DISTINCT option works for SQL, but not for PL/SQL
execute immediate q'[
select cast(collect(distinct tobj(num1, num2)) as tobj_tab)
from (select level num1, mod(level, :p_dist_val) num2 from dual connect by level <= :p_size)]'
into l_tobj_tab_1 using l_dist_val, p_size;
end if;
l_elapsed := dbms_utility.get_time - l_start_time;
helper.record(p_name, p_size, l_dist_val,
l_elapsed, helper.p_num1_cnt, (helper.p_num1_cnt/p_size));
helper.format(p_name||' count=', l_tobj_tab_1.count);
helper.format(' Total Number of Object Comparisons=', helper.p_num1_cnt);
end;
/
--exec collect_test('COLLECT_SORT', 100, 100);
--exec collect_test('COLLECT_SORT_DIST', 100, 10);
--exec collect_test('COLLECT_DISTINCT', 100, 100);
--exec collect_test('COLLECT_DISTINCT_DIST', 100, 10);
create or replace procedure collect_test_run(p_run number) as
begin
for i in 1..p_run loop collect_test('COLLECT_SORT', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('COLLECT_SORT_DIST', power(10, p_run), power(10, i)); end loop;
for i in 1..p_run loop collect_test('COLLECT_DISTINCT', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('COLLECT_DISTINCT_DIST', power(10, p_run), power(10, i)); end loop;
end;
/
--exec collect_test_run(3);
------------------------------- MULTISET_test ---------------------------------
create or replace procedure multiset_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_test_name varchar2(40);
l_tobj_tab_1 tobj_tab := tobj_tab();
l_tobj_tab_2 tobj_tab := tobj_tab();
l_tobj_tab_3 tobj_tab := tobj_tab();
l_start_time number;
l_elapsed number;
begin
select cast(multiset(select level, mod(level, l_dist_val) from dual connect by level <= p_size) as tobj_tab)
into l_tobj_tab_1 from dual;
select cast(multiset(select level, mod(level, l_dist_val) from dual connect by level <= p_size) as tobj_tab)
into l_tobj_tab_2 from dual;
helper.p_num1_cnt := 0;
l_start_time := dbms_utility.get_time;
case
when p_name in ('MULTISET_UNION_ALL', 'MULTISET_UNION_ALL_DIST') then
l_tobj_tab_3 := l_tobj_tab_1 MULTISET UNION ALL l_tobj_tab_2;
when p_name in ('MULTISET_UNION_DISTINCT', 'MULTISET_UNION_DISTINCT_DIST') then
l_tobj_tab_3 := l_tobj_tab_1 MULTISET UNION DISTINCT l_tobj_tab_2;
when p_name in ('MULTISET_EXCEPT_ALL', 'MULTISET_EXCEPT_ALL_DIST') then
l_tobj_tab_3 := l_tobj_tab_1 MULTISET EXCEPT ALL l_tobj_tab_2;
when p_name in ('MULTISET_EXCEPT_DISTINCT', 'MULTISET_EXCEPT_DISTINCT_DIST') then
l_tobj_tab_3 := l_tobj_tab_1 MULTISET EXCEPT DISTINCT l_tobj_tab_2;
when p_name in ('MULTISET_INTERSECT_ALL', 'MULTISET_INTERSECT_ALL_DIST') then
l_tobj_tab_3 := l_tobj_tab_1 MULTISET INTERSECT ALL l_tobj_tab_2;
when p_name in ('MULTISET_INTERSECT_DISTINCT', 'MULTISET_INTERSECT_DISTINCT_DIST') then
l_tobj_tab_3 := l_tobj_tab_1 MULTISET INTERSECT DISTINCT l_tobj_tab_2;
end case;
l_elapsed := dbms_utility.get_time - l_start_time;
helper.record(p_name, p_size, l_dist_val,
l_elapsed, helper.p_num1_cnt, (helper.p_num1_cnt/p_size/2));
helper.format(p_name||' count=', l_tobj_tab_3.count);
helper.format(' Total Number of Object Comparisons=', helper.p_num1_cnt);
end;
/
--exec multiset_test('MULTISET_UNION_ALL', 100, 100);
--exec multiset_test('MULTISET_UNION_DISTINCT', 100, 100);
--exec multiset_test('MULTISET_UNION_DISTINCT_DIST', 100, 10);
--exec multiset_test('MULTISET_EXCEPT_ALL', 100, 100);
--exec multiset_test('MULTISET_EXCEPT_DISTINCT', 100, 100);
--exec multiset_test('MULTISET_INTERSECT_ALL', 100, 100);
--exec multiset_test('MULTISET_INTERSECT_DISTINCT', 100, 100);
--exec multiset_test('MULTISET_INTERSECT_DISTINCT_DIST', 100, 100);
create or replace procedure multiset_test_run(p_run number) as
begin
for i in 1..p_run loop collect_test('MULTISET_UNION_ALL', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_UNION_ALL_DIST', power(10, p_run), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_UNION_DISTINCT', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_UNION_DISTINCT_DIST', power(10, p_run), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_EXCEPT_ALL', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_EXCEPT_ALL_DIST', power(10, p_run), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_EXCEPT_DISTINCT', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_EXCEPT_DISTINCT_DIST', power(10, p_run), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_INTERSECT_ALL', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_INTERSECT_ALL_DIST', power(10, p_run), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_INTERSECT_DISTINCT', power(10, i), power(10, i)); end loop;
for i in 1..p_run loop collect_test('MULTISET_INTERSECT_DISTINCT_DIST', power(10, p_run), power(10, i)); end loop;
end;
/
--exec multiset_test_run(3);
------------------------------- Test Control 2 ---------------------------------
create or replace procedure all_test_run_2(p_run number) as
begin
collect_test_run(p_run);
multiset_test_run(p_run);
end;
/
