Hi,
I would like to know if there is any difference in memory usage/allocation between the two examples below.
A single varchar column vs a multi column table type.
declare
type t_rec is record (id integer,
fname varchar2(20),
lname varchar2(20),
street varchar2(30),
hnum number(4)
);
type t_tab is table of rec;
l_tab tab;
begin
l_tab := t_tab();
select id
, first_name fname
, last_name lname
, street
, hnum
bulk collect into l_tab
from my_data;
end;
/
-- vs
declare
type t_tab is table of varchar2(200);
l_tab tab;
begin
l_tab := t_tab();
select ':id:'||to_char(id)||':first_name:'||first_name||':last_name:'||last_name||':street:'||street||':hnum:'||to_char(hnum)
bulk collect into l_tab
from my_data;
end;
/
That moment when you realise that someone didn't even take the time to see if their code would compile :-(
SQL> declare
2 type t_rec is record (id integer,
3 fname varchar2(20),
4 lname varchar2(20),
5 street varchar2(30),
6 hnum number(10)
7 );
8 type t_tab is table of rec;
9 l_tab tab;
10 begin
11 l_tab := t_tab();
12 select id
13 , first_name fname
14 , last_name lname
15 , street
16 , hnum
17 bulk collect into l_tab
18 from my_data;
19 end;
20 /
type t_tab is table of rec;
*
ERROR at line 8:
ORA-06550: line 8, column 25:
PLS-00201: identifier 'REC' must be declared
ORA-06550: line 8, column 2:
PL/SQL: Item ignored
ORA-06550: line 9, column 8:
PLS-00488: 'TAB' must be a type
ORA-06550: line 9, column 8:
PL/SQL: Item ignored
ORA-06550: line 11, column 2:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 11, column 2:
PL/SQL: Statement ignored
ORA-06550: line 12, column 9:
PL/SQL: ORA-00904: "ID": invalid identifier
ORA-06550: line 12, column 2:
PL/SQL: SQL Statement ignored
Anyway...once that was fixed, there are some differences in memory utilisation but you'd really have to be getting into very data volumes for it to be an issue
SQL> create table my_data as
2 select rownum id,
3 rpad('x',20,'x') first_name,
4 rpad('x',20,'x') last_name,
5 rpad('x',30,'x') street,
6 rownum hnum
7 from dual
8 connect by level <= 100000;
Table created.
SQL> declare
2 type t_rec is record (id integer,
3 fname varchar2(20),
4 lname varchar2(20),
5 street varchar2(30),
6 hnum number(10)
7 );
8 type t_tab is table of t_rec;
9 l_tab t_tab;
10 begin
11 l_tab := t_tab();
12 select id
13 , first_name fname
14 , last_name lname
15 , street
16 , hnum
17 bulk collect into l_tab
18 from my_data;
19 end;
20 /
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 s.name, st.value
3 from v$statname s, v$mystat st
4 where st.statistic# = s.statistic#
5 and s.name like 'session pga%';
NAME VALUE
---------------------------------------------------------------- ----------
session pga memory 3722696
session pga memory max 35245512
SQL> declare
2 type t_tab is table of varchar2(200);
3 l_tab t_tab;
4 begin
5 l_tab := t_tab();
6 select ':id:'||to_char(id)||':first_name:'||first_name||':last_name:'||last_name||':street:'||street||':hnum:'||to_char(hnum)
7 bulk collect into l_tab
8 from my_data;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> select
2 s.name, st.value
3 from v$statname s, v$mystat st
4 where st.statistic# = s.statistic#
5 and s.name like 'session pga%';
NAME VALUE
---------------------------------------------------------------- ----------
session pga memory 3722696
session pga memory max 20303304