We found that the memory usage and performance of Multidimensional Collections are dependent on the total number of branch nodes, which are determined by the data characteristics and the subscript indices ordering.
This Blog will try to demonstrate the findings with appended Test Code.
1. Know Your Data
At first, run two queries to understand the test data (see the Test Code):
SQL> select round(num_rows*avg_row_len/1024/1024) mb from dba_tables where table_name = 'MEM_TAB';
46 MB
SQL> select *
from (
select count(distinct id) id, count(distinct name) name
,count(distinct num_1) num_1, count(distinct num_2) num_2
,count(distinct txt_1) txt_1, count(distinct txt_2) txt_2
,count(distinct num_1||'.'||num_2) num_1_2, count(distinct txt_1||'.'||txt_2) txt_1_2
from mem_tab)
unpivot (distinct_count for count_name in
(id as 'count(distinct id)', name as 'count(distinct name)'
,num_1 as 'count(distinct num_1)', num_2 as 'count(distinct num_2)'
,txt_1 as 'count(distinct txt_1)', txt_2 as 'count(distinct txt_2)'
,num_1_2 as 'count(distinct num_1.num_2)'
,txt_1_2 as 'count(distinct txt_1.txt_2)'
));
COUNT_NAME DISTINCT_COUNT
--------------------------- --------------
count(distinct id) 1,000,000
count(distinct name) 1,000,000
count(distinct num_1) 500,000
count(distinct num_2) 50,000
count(distinct txt_1) 500,000
count(distinct txt_2) 50,000
count(distinct num_1.num_2) 1,000,000
count(distinct txt_1.txt_2) 1,000,000
- It has 2 unique columns (id and name), and two composite unique columns ((num_1.num_2), (txt_1.txt_2)).
- num_1 and txt_1 have 500,000 distinct values each, branching factor = 2, hence 500,000 branch nodes when storing as array(num_1)(num_2);
- num_2 and txt_2 have 50,000 distinct values each, branching factor = 20, hence 50,000 branch nodes when storing as array(num_2)(num_1).
2. Create Test
Run command:
SQL> exec test_collection_dimm_mem.run(1000000);
0 Dimension 1 array(id) , pga_used(MB) = 411, Elapsed(Second)= 5.44
1 Dimension 1 array(name), pga_used(MB) = 459, Elapsed(Second)= 6.21
2 Dimension 2 array(num_1)(num_2), pga_used(MB) = 2227, Elapsed(Second)= 8.54
3 Dimension 2 array(num_2)(num_1), pga_used(MB) = 536, Elapsed(Second)= 5.44
4 Dimension 2 array(txt_1)(txt_2), pga_used(MB) = 2739, Elapsed(Second)= 7.79
5 Dimension 2 array(txt_2)(txt_1), pga_used(MB) = 628, Elapsed(Second)= 5.96
6 Dimension 2 array(num_1)(txt_2), pga_used(MB) = 2719, Elapsed(Second)= 6.53
7 Dimension 2 array(num_2)(txt_1), pga_used(MB) = 626, Elapsed(Second)= 5.62
8 Dimension 2 array(txt_1)(num_2), pga_used(MB) = 2251, Elapsed(Second)= 9.12
9 Dimension 2 array(txt_2)(num_1), pga_used(MB) = 539, Elapsed(Second)= 6.07
10 Dimension 1 table(id), pga_used(MB) = 411, Elapsed(Second)= 6.25
11 Dimension 2 table(num_1)(num_2), pga_used(MB) = 889, Elapsed(Second)= 9.78
12 Dimension 2 table(num_2)(num_1), pga_used(MB) = 443, Elapsed(Second)= 7.86
- One dimensional associative array has a factor 10 more memory usage compared to disk table size (46MB).
- Comparing RUN 4 and RUN 5, two dimensional can have a factor 60 more memory usage (2739/46) when organized with 500,000 branch nodes in array(txt_1)(txt_2);
but factor 14 (628/46) when organized with 50,000 branch nodes in array(txt_2)(txt_1) by simply exchanging the subscripts;
More than a factor 4 of difference when exchanging the order of subscripts.
Each branch node takes about 5KB to 13KB.
(imagine the cases of organizations with 50% vs. 5% employees being managers) - One dimensional nested table and associative array are comparable.
- Two dimensional nested tables have a factor of 19 (888/46), and 10 (443/46).
- The performance is proportional to the memory usage.
3. Lookup Test
Once the data is stored in array/table, they can be used for in-memory search.
For example, in Test Code: test_collection_dimm_mem.run, reactivate the lookup snippet code:
---- lookup example ----
l_start_time := dbms_utility.get_time;
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_node_rec := l_array_d2nn(c.num_1)(c.num_2);
end loop;
But nested table is a factor 3 to 5 faster.
0 Dimension 1 array(id) , pga_used(MB) = 411, Elapsed(Second)= 2.27
1 Dimension 1 array(name), pga_used(MB) = 459, Elapsed(Second)= 2.88
2 Dimension 2 array(num_1)(num_2), pga_used(MB) = 2227, Elapsed(Second)= 2.85
3 Dimension 2 array(num_2)(num_1), pga_used(MB) = 536, Elapsed(Second)= 2.51
4 Dimension 2 array(txt_1)(txt_2), pga_used(MB) = 2739, Elapsed(Second)= 3.3
5 Dimension 2 array(txt_2)(txt_1), pga_used(MB) = 628, Elapsed(Second)= 2.95
6 Dimension 2 array(num_1)(txt_2), pga_used(MB) = 2719, Elapsed(Second)= 2.77
7 Dimension 2 array(num_2)(txt_1), pga_used(MB) = 626, Elapsed(Second)= 2.58
8 Dimension 2 array(txt_1)(num_2), pga_used(MB) = 2251, Elapsed(Second)= 3.4
9 Dimension 2 array(txt_2)(num_1), pga_used(MB) = 539, Elapsed(Second)= 2.93
10 Dimension 1 table(id), pga_used(MB) = 411, Elapsed(Second)= .45
11 Dimension 2 table(num_1)(num_2), pga_used(MB) = 889, Elapsed(Second)= 1.09
12 Dimension 2 table(num_2)(num_1), pga_used(MB) = 443, Elapsed(Second)= .8
4. Heapdump Dump
Make heapdump for 3 Cases:
- Dimension 1 array(id) in RUN 0
- Dimension 2 array(num_1)(num_2) in RUN 2
- Dimension 2 array(num_2)(num_1) in RUN 3
alter session set events 'immediate trace name heapdump level 16';
--- Dimension 1 array(id) ---
HEAP DUMP heap name="top uga heap" desc=0x7f4b2d56ada0
Total heap size =427532128
--- Dimension 2 array(num_1)(num_2) ---
HEAP DUMP heap name="top uga heap" desc=0x7f94fddf3da0
Total heap size =2331900936
--- Dimension 2 array(num_2)(num_1) ---
HEAP DUMP heap name="top uga heap" desc=0x7fb10cf26da0
Total heap size =558883712
5. PGA Dump
Further make pga_detail_dump by:
alter session set events 'immediate trace name pga_detail_dump level 27';
(note 27 is Oracle process number (PID), only top categories are listed)
--- Dimension 1 array(id) ---
220672920 bytes,13515 chunks: "pmuccst: adt/record " PL/SQL
koh-kghu sessi ds=0x7f4b27f791b8 dsprt=0x7f4b28eed740
182716608 bytes,11196 chunks: "pl/sql vc2 " PL/SQL
koh-kghu sessi ds=0x7f4b27f791b8 dsprt=0x7f4b28eed740
18667040 bytes,1144 chunks: "pmucalm coll " PL/SQL
koh-kghu sessi ds=0x7f4b27f791b8 dsprt=0x7f4b28eed740
973992 bytes, 66 chunks: "static frame of inst " PL/SQL
koh-kghu sessi ds=0x7f4b282a8580 dsprt=0x7f4b28eed740
--- Dimension 2 array(num_1)(num_2) ---
1633408272 bytes,100038 chunks: "pmucalm coll " PL/SQL
koh-kghu sessi ds=0x7f94f88121b8 dsprt=0x7f94f977d740
282131512 bytes,17279 chunks: "pmuccst: adt/record " PL/SQL
koh-kghu sessi ds=0x7f94f88121b8 dsprt=0x7f94f977d740
187843616 bytes,11510 chunks: "pl/sql vc2 " PL/SQL
koh-kghu sessi ds=0x7f94f88121b8 dsprt=0x7f94f977d740
117545272 bytes,7199 chunks: "pmucpcon: ipm " PL/SQL
koh-kghu sessi ds=0x7f94f88121b8 dsprt=0x7f94f977d740
57776680 bytes,3539 chunks: "pmucpcon: tds " PL/SQL
koh-kghu sessi ds=0x7f94f88121b8 dsprt=0x7f94f977d740
41783352 bytes,2559 chunks: "pmucpcon: cds " PL/SQL
koh-kghu sessi ds=0x7f94f88121b8 dsprt=0x7f94f977d740
--- Dimension 2 array(num_2)(num_1) ---
243923992 bytes,14939 chunks: "pmuccst: adt/record " PL/SQL
koh-kghu sessi ds=0x7fb1079451b8 dsprt=0x7fb1088ad740
160771776 bytes,9852 chunks: "pl/sql vc2 " PL/SQL
koh-kghu sessi ds=0x7fb1079451b8 dsprt=0x7fb1088ad740
127166584 bytes,7789 chunks: "pmucalm coll " PL/SQL
koh-kghu sessi ds=0x7fb1079451b8 dsprt=0x7fb1088ad740
10956088 bytes, 671 chunks: "pmucpcon: ipm " PL/SQL
koh-kghu sessi ds=0x7fb1079451b8 dsprt=0x7fb1088ad740
5747456 bytes, 352 chunks: "pmucpcon: cds " PL/SQL
koh-kghu sessi ds=0x7fb1079451b8 dsprt=0x7fb1088ad740
4416776 bytes, 271 chunks: "pmucpcon: tds " PL/SQL
koh-kghu sessi ds=0x7fb1079451b8 dsprt=0x7fb1088ad740
- "pl/sql vc2" are all involved varchar2 strings.
- "pmuccst: adt/record" (ADT: Abstract Data Type) stores all the PL/SQL records, in this test, it is 1,000,000.
- "pmucalm coll" looks like all the allocated collections, which represent the branch nodes. array(num_1)(num_2) takes 1,633,408,272 Bytes, whereas array(num_2)(num_1) 127,166,584 since the first one has 500,000 branch nodes, whereas second 50,000.
- "pmuccst: adt/record" stands for leaf nodes, that is the number of elements.
- "pmucalm coll" represents branch nodes, that is the number of collections.
6. Populate process_memory_detail
Dump process_memory_detail into process_memory_detail_v by setting event PGA_DETAIL_GET, then query the result with:
column run_name format A40
column "NAME(MB, ALLOC_COUNT) List" format A200
column name format A30
select run, run_name, pid, round(sum(bytes)/1024/1024) mb, sum(allocation_count) allocation_count
from process_memory_detail_v
group by run, run_name, pid
order by run;
RUN RUN_NAME PID MB ALLOCATION_COUNT
---- -------------------------------- ---- ------- ----------------
0 Dimension 1 array(id) 27 409 29,676
1 Dimension 1 array(name) 27 458 32,824
2 Dimension 2 array(num_1)(num_2) 27 2,220 145,969
3 Dimension 2 array(num_2)(num_1) 27 534 37,721
4 Dimension 2 array(txt_1)(txt_2) 27 2,730 178,767
5 Dimension 2 array(txt_2)(txt_1) 27 626 43,632
6 Dimension 2 array(num_1)(txt_2) 27 2,711 177,517
7 Dimension 2 array(num_2)(txt_1) 27 624 43,511
8 Dimension 2 array(txt_1)(num_2) 27 2,244 147,554
9 Dimension 2 array(txt_2)(num_1) 27 537 37,895
10 Dimension 1 table(id) 27 411 29,745
11 Dimension 2 table(num_1)(num_2) 27 886 60,281
12 Dimension 2 table(num_2)(num_1) 27 443 31,829
-------------------------------------------------------------------
select run, run_name, pid, round(sum(bytes)/1024/1024) mb,
(select listagg(rpad(name||'('||round(bytes/1024/1024)||', '||allocation_count||')', 40, chr(32)), '; ')
within group (order by bytes desc) agg
from (select * from process_memory_detail_v c
where c.run = v.run and c.pid = v.pid and bytes > 0
order by bytes desc) c
where rownum <= 3
) "NAME(MB, ALLOC_COUNT) List"
from process_memory_detail_v v
group by run, run_name, pid
order by run, run_name, pid;
RUN RUN_NAME PID MB NAME(MB, ALLOC_COUNT) List
--- ------------------------------- ---- ------ ---------------------------------------------------------------------------------------------------
0 Dimension 1 array(id) 27 409 pmuccst: adt/record(225, 14456) ; pl/sql vc2(161, 10311) ; pmucalm coll(17, 1084)
1 Dimension 1 array(name) 27 458 pl/sql vc2(196, 12561) ; pmuccst: adt/record(190, 12220) ; pmucalm coll(65, 4196)
2 Dimension 2 array(num_1)(num_2) 27 2,220 pmucalm coll(1560, 100167) ; pmuccst: adt/record(271, 17379) ; pl/sql vc2(179, 11488)
3 Dimension 2 array(num_2)(num_1) 27 534 pmuccst: adt/record(231, 14827) ; pl/sql vc2(153, 9836) ; pmucalm coll(123, 7902)
4 Dimension 2 array(txt_1)(txt_2) 27 2,730 pmucalm coll(2699, 173350) ; pmuccst: adt/record(14, 882) ; pl/sql vc2(10, 675)
5 Dimension 2 array(txt_2)(txt_1) 27 626 pmucalm coll(229, 14676) ; pmuccst: adt/record(207, 13302) ; pl/sql vc2(176, 11272)
6 Dimension 2 array(num_1)(txt_2) 27 2,711 pmucalm coll(2596, 166684) ; pmuccst: adt/record(62, 3975) ; pl/sql vc2(47, 2993)
7 Dimension 2 array(num_2)(txt_1) 27 624 pmucalm coll(222, 14238) ; pmuccst: adt/record(221, 14194) ; pl/sql vc2(167, 10739)
8 Dimension 2 array(txt_1)(num_2) 27 2,244 pmucalm coll(1570, 100838) ; pmuccst: adt/record(273, 17546) ; pl/sql vc2(185, 11904)
9 Dimension 2 array(txt_2)(num_1) 27 537 pmuccst: adt/record(232, 14931) ; pl/sql vc2(155, 9946) ; pmucalm coll(123, 7870)
10 Dimension 1 table(id) 27 411 pmuccst: adt/record(222, 14276) ; pl/sql vc2(163, 10488) ; pmucalm coll(17, 1089)
11 Dimension 2 table(num_1)(num_2) 27 886 pmucalm coll(286, 18381) ; pmuccst: adt/record(248, 15936) ; pl/sql vc2(144, 9229)
12 Dimension 2 table(num_2)(num_1) 27 443 pmuccst: adt/record(229, 14680) ; pl/sql vc2(155, 9951) ; pmucalm coll(30, 1905)
----------------------------------------------------------------------------------------------------------
select run, run_name, pid, category, name, heap_name, round(bytes/1024/1024) mb
,allocation_count, round(bytes/allocation_count) bytes_per_cnt
from process_memory_detail_v
where name = 'pmucalm coll'
order by pid, category, name, heap_name, run, timestamp;
RUN RUN_NAME PID CATEGORY NAME HEAP_NAME MB ALLOCATION_COUNT BYTES_PER_CNT
--- ------------------------------- ---- ---------- ------------- ---------------- ------ ----------------- --------------
0 Dimension 1 array(id) 27 PL/SQL pmucalm coll koh-kghu sessi 17 1,084 16,317
1 Dimension 1 array(name) 27 PL/SQL pmucalm coll koh-kghu sessi 65 4,196 16,325
2 Dimension 2 array(num_1)(num_2) 27 PL/SQL pmucalm coll koh-kghu sessi 1,560 100,167 16,328
3 Dimension 2 array(num_2)(num_1) 27 PL/SQL pmucalm coll koh-kghu sessi 123 7,902 16,326
4 Dimension 2 array(txt_1)(txt_2) 27 PL/SQL pmucalm coll koh-kghu sessi 2,699 173,350 16,328
5 Dimension 2 array(txt_2)(txt_1) 27 PL/SQL pmucalm coll koh-kghu sessi 229 14,676 16,327
6 Dimension 2 array(num_1)(txt_2) 27 PL/SQL pmucalm coll koh-kghu sessi 2,596 166,684 16,328
7 Dimension 2 array(num_2)(txt_1) 27 PL/SQL pmucalm coll koh-kghu sessi 222 14,238 16,327
8 Dimension 2 array(txt_1)(num_2) 27 PL/SQL pmucalm coll koh-kghu sessi 1,570 100,838 16,328
9 Dimension 2 array(txt_2)(num_1) 27 PL/SQL pmucalm coll koh-kghu sessi 123 7,870 16,326
10 Dimension 1 table(id) 27 PL/SQL pmucalm coll koh-kghu sessi 17 1,089 16,317
11 Dimension 2 table(num_1)(num_2) 27 PL/SQL pmucalm coll koh-kghu sessi 286 18,381 16,327
12 Dimension 2 table(num_2)(num_1) 27 PL/SQL pmucalm coll koh-kghu sessi 30 1,905 16,322
- The first query shows the memory usage per run.
- The second query lists the memory allocations of top 3 Heaps for each run.
- The third query reveals Heap "pmucalm coll" statistics of memory and allocation_count.
The top 4 RUNs (2, 4, 6, 8) have 500,000 branch nodes, and hence more memory and allocation_count.
Computing the average bytes per allocation, BYTES_PER_CNT shows almost a constant value of 16K.
Probably generous reserving 16K (2x8K-Blocks) for each "pmucalm coll" conforms to the design strategy of preventing expensive branch node splitting.
7. Test Code
set numformat 999,999,999
drop table mem_tab;
create table mem_tab(id number, name varchar2(10)
,num_1 number, num_2 number
,txt_1 varchar2(10), txt_2 varchar2(10));
insert into mem_tab
select level id, rpad(level, 10, 'X') name
,level num_1, ceil(level/20) num_2
,rpad(level, 10, 'X') txt_1, rpad(ceil(level/20), 10, 'X') txt_2
from dual connect by level <= 500000
union all
select (level + 500000) id, rpad((level + 500000), 10, 'X') name
,level num_1, -ceil(level/20) num_2
,rpad(level, 10, 'X') txt_1, rpad(-ceil(level/20), 10, 'X') txt_2
from dual connect by level <= 500000;
commit;
exec dbms_stats.gather_table_stats(null, 'MEM_TAB');
select round(num_rows*avg_row_len/1024/1024) mb from dba_tables where table_name = 'MEM_TAB';
-- 46 MB
select *
from (
select count(distinct id) id, count(distinct name) name
,count(distinct num_1) num_1, count(distinct num_2) num_2
,count(distinct txt_1) txt_1, count(distinct txt_2) txt_2
,count(distinct num_1||'.'||num_2) num_1_2, count(distinct txt_1||'.'||txt_2) txt_1_2
from mem_tab)
unpivot (distinct_count for count_name in
(id as 'count(distinct id)', name as 'count(distinct name)'
,num_1 as 'count(distinct num_1)', num_2 as 'count(distinct num_2)'
,txt_1 as 'count(distinct txt_1)', txt_2 as 'count(distinct txt_2)'
,num_1_2 as 'count(distinct num_1.num_2)'
,txt_1_2 as 'count(distinct txt_1.txt_2)'
));
create or replace package test_collection_dimm_mem as
procedure run (p_cnt number := 1000000);
end;
/
drop table process_memory_detail_v;
create table process_memory_detail_v as
select 123 run, rpad('A', 40, 'X') run_name, timestamp'1998-02-17 11:22:00' timestamp
,234 session_id, 345 session_serial#, v.*
from v$process_memory_detail v where 1=2;
create or replace package body test_collection_dimm_mem as
type t_rec is record (
id number
,name varchar2(10)
,num_1 number
,num_2 number
,txt_1 varchar2(10)
,txt_2 varchar2(10)
);
type t_array_d1n is table of t_rec index by pls_integer; -- array(number)
type t_array_d1t is table of t_rec index by varchar2(30); -- array(text)
type t_array_d2nn is table of t_array_d1n index by pls_integer; -- array(number)(number)
type t_array_d2tt is table of t_array_d1t index by varchar2(30); -- array(text)(text)
type t_array_d2nt is table of t_array_d1t index by pls_integer; -- array(number)(text)
type t_array_d2tn is table of t_array_d1n index by varchar2(30); -- array(text)(number)
type t_nesttab_d1 is table of t_rec;
type t_nesttab_d2 is table of t_nesttab_d1;
l_node_rec t_rec;
l_array_d1n t_array_d1n; -- array(number)
l_array_d1t t_array_d1t; -- array(text)
l_array_d2nn t_array_d2nn; -- array(number)(number)
l_array_d2tt t_array_d2tt; -- array(text)(text)
l_array_d2nt t_array_d2nt; -- array(number)(text)
l_array_d2tn t_array_d2tn; -- array(text)(number)
l_nesttab_d1 t_nesttab_d1 := new t_nesttab_d1(); -- nested Table (number)
l_nesttab_d2 t_nesttab_d2 := new t_nesttab_d2(); -- nested Table (number)(number)
l_node_map_sep varchar2(1) := '.';
l_start_time number;
l_mb varchar2(10);
l_last_num number;
l_pid number;
l_sid number;
l_serial# number;
l_run number := 0;
l_pga_status varchar2(10) := 'NOT';
procedure reset as
begin
l_array_d1n.delete;
l_array_d1t.delete;
l_array_d2nn.delete;
l_array_d2tt.delete;
l_array_d2nt.delete;
l_array_d2tn.delete;
l_nesttab_d1.delete;
l_nesttab_d2.delete;
dbms_session.free_unused_user_memory;
l_start_time := dbms_utility.get_time;
end;
procedure prt_and_reset_mem(p_name varchar2) as
begin
select lpad(round(pga_used_mem/1024/1024), 5, ' '), pid, s.sid, s.serial#
into l_mb, l_pid, l_sid, l_serial#
from v$process p, v$session s
where p.addr = s.paddr and s.sid = sys.dbms_support.mysid and rownum = 1;
dbms_output.put_line(rpad(l_run, 3, ' ')||p_name||', pga_used(MB) = '||l_mb||
', Elapsed(Second)= '||round((dbms_utility.get_time - l_start_time)/100, 2));
execute immediate q'[alter session set events 'immediate trace name PGA_DETAIL_GET level ]'||l_pid||q'[']';
-- wait status = COMPLETE when status = SCANNING
while (true) loop
select status into l_pga_status from v$process_memory_detail_prog where pid = l_pid;
exit when l_pga_status = 'COMPLETE';
dbms_lock.sleep(0.1);
end loop;
delete from process_memory_detail_v
where pid = l_pid and session_id = l_sid and session_serial# = l_serial# and run = l_run;
insert into process_memory_detail_v select l_run, p_name, systimestamp, l_sid, l_serial#, v.*
from v$process_memory_detail v where pid = l_pid;
-- outcomment to keep last PGA_DETAIL
-- execute immediate q'[alter session set events 'immediate trace name PGA_DETAIL_CANCEL level ]'||l_pid||q'[']';
commit;
l_run := l_run + 1;
reset;
end;
function rec(id number, name varchar2, num_1 number, num_2 number, txt_1 varchar2, txt_2 varchar2)
return t_rec as
begin
l_node_rec.id := id;
l_node_rec.name := name;
l_node_rec.num_1 := num_1;
l_node_rec.num_2 := num_2;
l_node_rec.txt_1 := txt_1;
l_node_rec.txt_2 := txt_2;
return l_node_rec;
end;
procedure run (p_cnt number := 1000000) as
begin
reset;
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d1n(c.id) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 1 array(id) ');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d1t(c.name) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 1 array(name)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2nn(c.num_1)(c.num_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
---- lookup example ----
--l_start_time := dbms_utility.get_time;
--for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
-- l_node_rec := l_array_d2nn(c.num_1)(c.num_2);
--end loop;
prt_and_reset_mem('Dimension 2 array(num_1)(num_2)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2nn(c.num_2)(c.num_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(num_2)(num_1)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2tt(c.txt_1)(c.txt_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(txt_1)(txt_2)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2tt(c.txt_2)(c.txt_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(txt_2)(txt_1)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2nt(c.num_1)(c.txt_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(num_1)(txt_2)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2nt(c.num_2)(c.txt_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(num_2)(txt_1)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2tn(c.txt_1)(c.num_2) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(txt_1)(num_2)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab where rownum <= p_cnt) loop
l_array_d2tn(c.txt_2)(c.num_1) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 2 array(txt_2)(num_1)');
--- nested table. Note: specify p_cnt = 1000000 to select all rows for test ---
dbms_output.put_line('');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab
where rownum <= p_cnt order by id) loop
l_nesttab_d1.extend;
l_nesttab_d1(l_nesttab_d1.last) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
end loop;
prt_and_reset_mem('Dimension 1 table(id)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab
where rownum <= p_cnt order by num_1, num_2) loop
if (c.num_1 != l_last_num) then
l_nesttab_d2.extend;
l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
l_nesttab_d1 := new t_nesttab_d1();
end if;
l_nesttab_d1.extend;
l_nesttab_d1(l_nesttab_d1.last) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
l_last_num := c.num_1;
end loop;
l_nesttab_d2.extend;
l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
---- lookup example ----
--l_start_time := dbms_utility.get_time;
--for i in 1..l_nesttab_d2.count loop
-- l_nesttab_d1 := l_nesttab_d2(i);
-- for j in 1..l_nesttab_d1.count loop
-- l_node_rec := l_nesttab_d1(j);
-- end loop;
--end loop;
prt_and_reset_mem('Dimension 2 table(num_1)(num_2)');
for c in (select id, name, num_1, num_2, txt_1, txt_2 from mem_tab
where rownum <= p_cnt order by num_2, num_1) loop
if (c.num_2 != l_last_num) then
l_nesttab_d2.extend;
l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
l_nesttab_d1 := new t_nesttab_d1();
end if;
l_nesttab_d1.extend;
l_nesttab_d1(l_nesttab_d1.last) := rec(c.id, c.name, c.num_1, c.num_2, c.txt_1, c.txt_2);
l_last_num := c.num_2;
end loop;
l_nesttab_d2.extend;
l_nesttab_d2(l_nesttab_d2.last) := l_nesttab_d1;
prt_and_reset_mem('Dimension 2 table(num_2)(num_1)');
end;
end;
/
-- set serveroutput on
-- exec test_collection_dimm_mem.run(1000000);
