上一篇讲到了调用集锦，这篇关注一下性能问题吧。
DECLARE CURSOR c_tool_list IS SELECT descr d1 FROM hardware; l_descr hardware.descr%type;BEGIN OPEN c_tool_list; LOOP FETCH c_tool_list INTO l_descr; EXIT WHEN c_tool_list%notfound; END LOOP; CLOSE c_tool_list;END;
上面的语句在小陈机子上运行时间：
Elapsed: 00:00:09.714
下面的语句又需要多久呢？
DECLARE CURSOR c_tool_list IS SELECT descr d2 FROM hardware;type t_descr_listIS TABLE OF c_tool_list%rowtype; l_descr_list t_descr_list;BEGIN OPEN c_tool_list; FETCH c_tool_list bulk collect INTO l_descr_list; CLOSE c_tool_list;END;
Elapsed: 00:00:00.667答案是：
两个语句比较，上面的自然是简洁上占了优势，也少写了代码。不过从性能上，完全后者逆袭呀。真是海水不可斗量，人不可貌相。
下面我们来看看 collection type 的性能比拼
SET timing ONDECLAREtype t_va IS varray(1000) OF NUMBER;type t_ntIS TABLE OF NUMBER;type t_aaIS TABLE OF NUMBER INDEX BY pls_integer; va t_va; nt t_nt; aa t_aa;BEGIN FOR i IN 1 .. 10000 LOOP SELECT rownum -- -- Comment in the collection type you want to test -- bulk collect INTO va --bulk collect into nt --bulk collect into aa FROM dual CONNECT BY level <= 1000 ; END LOOP;END;
l varray(1000) OF NUMBER;
l TABLE OF NUMBER;
l TABLE OF NUMBER INDEX BY pls_integer;
执行时间分别是：
l Elapsed: 00:00:09.234
l Elapsed: 00:00:09.272
l Elapsed: 00:00:09.472
看来时间上相差不大。
然后介绍一下LIMIT用法,书中的内存一直在增加，而小流氓的一直没有增加，神马情况？
set serveroutput on;cl scr;DECLAREtype t_row_listIS TABLE OF hardware.descr%type; l_rows t_row_list; l_pga_ceiling NUMBER(10);type t_fetch_sizeIS TABLE OF pls_integer; l_fetch_sizes t_fetch_size := t_fetch_size(5,10,50,100,500,1000,10000,100000,1000000); rc sys_refcursor;BEGIN SELECT value INTO l_pga_ceiling FROM v$mystat m, v$statname s WHERE s.statistic# = m.statistic# AND s.name = 'session pga memory max'; dbms_output.put_line('Initial PGA: '||l_pga_ceiling); FOR i IN 1 .. l_fetch_sizes.count LOOP OPEN rc FOR SELECT descr FROM hardware; LOOP FETCH rc bulk collect INTO l_rows limit l_fetch_sizes(i); EXIT WHEN rc%notfound; END LOOP; CLOSE rc; SELECT value INTO l_pga_ceiling FROM v$mystat m, v$statname s WHERE s.statistic# = m.statistic# AND s.name = 'session pga memory max'; dbms_output.put_line('Fetch size: '||l_fetch_sizes(i)); dbms_output.put_line('- PGA Max: '||l_pga_ceiling); END LOOP;END;
再来看看处理1000*1000*1000条数据，书中作者的报内存溢出了。ORA-04030: out of process memory when trying to allocate 16396 bytes
.
当然小陈的本本也不例外。只是错误不一样了。所以一定要配合LIMIT来使用，所以一定要配合LIMIT来使用，所以一定要配合LIMIT来使用。重要的事情说三次！！！
ORA-04036: 实例使用的 PGA 内存超出 PGA_AGGREGATE_LIMIT
cl scr;DECLAREtype t_huge_setIS TABLE OF NUMBER; l_the_server_slaminator t_huge_set;BEGIN SELECT rownum bulk collect INTO l_the_server_slaminator FROM ( SELECT level FROM dual CONNECT BY level <= 1000), ( SELECT level FROM dual CONNECT BY level <= 1000), ( SELECT level FROM dual CONNECT BY level <= 1000);END;