Oracle 内存使用单列varchar2(2000) 与多列表类型

嗨，

我想知道下面两个示例之间的内存使用/分配是否有任何差异。
单个varchar列与多列表类型。

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;
/

当你意识到有人甚至没有花时间看看他们的代码是否会编译的那一刻:-(

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

无论如何... 一旦解决了该问题，内存利用率就会有所不同，但是您实际上必须进入非常大的数据量才能成为问题

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