批量SQL操作之批量获取

0 前言

PL/SQL中的批量操作，能一次操作和处理许多行，而不是一次一行。批量获取操作是指一次调用把数据库一组行读入PL/SQL结构中，而不是每读入一行就要发起一次对数据库的调用。

1 设置数值案例

  1 -----创建“测试表”

  2 CREATE TABLE hardware

  3 (

  4 aisle NUMBER(5),

  5 item NUMBER(12),

  6 descr CHAR(50)

  7 );

  8

  9 ------ 使用dual生成任意行技巧

 10 INSERT /* +Append */ INTO hardware

 11 SELECT TRUNC(ROWNUM/1000)+1 aisle,

 12        ROWNUM item,

 13        'Description' || ROWNUM descr

 14 FROM

 15   (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000),

 16   (SELECT 1 FROM dual CONNECT BY LEVEL <= 1000);

2 在PL/SQL中执行批量操作

2.1 早先PL/SQL提取数据行的方式

在引入批量操作之前(oracle8.1前)，PL/SQL提取数据行的方式

（1）隐式游标

    一个标准的SQL查询（select-into）用于从表中提取一行数据，或提取该行中的若干列保存到目标变量。如果没有提取行或提取数据超过一行，就会引发异常。下面是一个例子：

SQL> -- 隐式游标

SQL> DECLARE

  2    l_descr hardware.descr%TYPE;

  3  BEGIN

  4    SELECT descr

  5    INTO   l_descr

  6    FROM   hardware

  7    WHERE  aisle = 1

  8    AND    item=1;

  9    dbms_output.put_line(l_descr);

 10  END;

 11  /

PL/SQL procedure successfully completed

（2）显示获取调用

    游标在PL/SQL声明部分定义，然后打开这个游标，利用一个循环从游标中取出数据，直到行数末尾，再关闭游标。下面是一个例子：

SQL> -- 显示获取调用

SQL> DECLARE

  2    CURSOR c_tool_list IS

  3      SELECT descr

  4      FROM   hardware

  5      WHERE  aisle = 1

  6      AND    item BETWEEN 1 AND 500;

  7 

  8    l_descr hardware.descr%TYPE;

  9  BEGIN

 10    OPEN c_tool_list;

 11    LOOP

 12      FETCH c_tool_list INTO l_descr;

 13      dbms_output.put_line(l_descr);  --Ora-20000: buffer overflow,则PL/SQL Developer 缓冲区大小

 14      EXIT WHEN c_tool_list%NOTFOUND;

 15    END LOOP;

 16    CLOSE c_tool_list;

 17    dbms_output.put_line(l_descr);

 18  END;

 19  /

PL/SQL procedure successfully completed

（3）隐式获取调用

   For循环执行游标管理，游标在for循环中进行编码。

SQL> -- 隐式获取调用

SQL> BEGIN

  2    FOR i IN (

  3      SELECT descr

  4      FROM   hardware

  5      WHERE  aisle = 1

  6      AND    item BETWEEN 1 AND 500

  7      )

  8    LOOP

  9       dbms_output.put_line(i.descr);  --Ora-20000: buffer overflow,则PL/SQL Developer 缓冲区大小

 10    END LOOP;

 11  END;

 12  /

PL/SQL procedure successfully completed

2.2 三种常用的批量收集及调用形式

（1）隐式游标批量模式

    只需在一个标准的SQL查询（select-into）中添加bulk collect关键字，实现把多行数据提取到一个集合类型中。下面是一个例子：

SQL> -- 隐式游标批量模式

SQL> DECLARE

  2    TYPE t_descr_list IS TABLE OF hardware.descr%TYPE;

  3    l_descr_list t_descr_list;

  4  BEGIN

  5    SELECT descr

  6    BULK COLLECT

  7    INTO l_descr_list

  8    FROM hardware

  9    WHERE aisle=1

 10    AND item BETWEEN 1 AND 100;

 11    dbms_output.put_line(l_descr_list(100));

 12    FOR i IN 1..100 LOOP

 13      dbms_output.put_line(l_descr_list(i));  --Ora-20000: buffer overflow,则PL/SQL Developer 缓冲区大小

 14    END LOOP;

 15  END;

 16  /

PL/SQL procedure successfully completed

（2）显示批量获取调用模式

    定义一个集合类型保存结果，并在Fetch命令中添加bulk collect子句，就可以在一次调用中把游标结果集中的所有行提取到集合类型的变量中去。下面是一个例子：

SQL> -- 显示批量获取调用模式

SQL> DECLARE

  2    CURSOR c_tool_list IS

  3      SELECT descr, item

  4      FROM hardware

  5      WHERE aisle=1

  6      AND item BETWEEN 1 AND 500;

  7 

  8    TYPE t_descr_list IS TABLE OF c_tool_list%ROWTYPE;

  9    l_descr_list t_descr_list;

 10  BEGIN

 11    OPEN c_tool_list;

 12    FETCH c_tool_list BULK COLLECT INTO l_descr_list;

 13    CLOSE c_tool_list;

 14 

 15   for r in l_descr_list.first .. l_descr_list.last loop

 16     dbms_output.put_line(l_descr_list(r).descr);

 17     dbms_output.put_line(l_descr_list(r).item);

 18   end loop;

 19  END;

 20  /

PL/SQL procedure successfully completed

（3）隐式批量获取调用模式

    Oracle10g之后，对for循环的“自动批量收集”增强。按照定义，对游标使用for循环是要从for循环中获取所有行，除非在循环内部有明确的exit命令，那么PL/SQL编译器就可以安全地采用批量收集优化。使用oracle10g及以上版本，数据库参数plsql_optimize_level设置至少为2（默认），就会自动获得这样的优化。下面是一个例子：

SQL> -- 隐式批量获取调用模式

SQL> SELECT banner FROM v$version WHERE ROWNUM=1; --查询数据库版本信息

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> SELECT VALUE FROM v$parameter WHERE NAME='plsql_optimize_level';  --优化参数查询

VALUE

--------------------------------------------------------------------------------

2

SQL> BEGIN

  2    FOR i IN (

  3      SELECT descr

  4      FROM hardware

  5      WHERE aisle=1

  6      AND item BETWEEN 1 AND 500

  7      )

  8    LOOP

  9      NULL;

 10      dbms_output.put_line(i.descr);

 11    END LOOP;

 12  END;

 13  /

PL/SQL procedure successfully completed

2.3 针对不同集合式数据类型的批量获取的行保存

2.3.1三种集合式的数据类型

n  Varray(可变数组)

n  Nested table(嵌套表)

n  Associative array(关联数组)

2.3.2 批量操作时间响应对比

（1）逐行代码与等价的批量操作响应时间对比

case1：逐行代码数据收集举例

SQL> PURGE RECYCLEBIN; --;--清空当前用户的回收站

Done

Executed in 0.125 seconds

SQL> alter system flush buffer_cache;  ---清空缓存

System altered

Executed in 0.203 seconds

SQL> SET timing ON --显示时间

SQL> DECLARE

  2    CURSOR c_tool_list IS

  3      SELECT descr d1

  4      FROM hardware;

  5    l_descr hardware.descr%TYPE;

  6  BEGIN

  7    OPEN c_tool_list;

  8    LOOP

  9      FETCH c_tool_list INTO l_descr;

 10      EXIT WHEN c_tool_list%NOTFOUND;

 11    END LOOP;

 12    CLOSE c_tool_list;

 13  END;

 

 14  /

PL/SQL procedure successfully completed

Executed in 14.509 seconds

case2：批量数据收集举例

SQL> PURGE RECYCLEBIN; --;--清空当前用户的回收站

Done

SQL> alter system flush buffer_cache;  ---清空缓存

System altered

SQL> SET timing ON --显示时间

SQL> DECLARE

  2    CURSOR c_tool_list IS

  3      SELECT descr d2

  4      FROM hardware;

  5 

  6    TYPE t_descr_list IS TABLE OF c_tool_list%ROWTYPE;

  7    l_descr_list t_descr_list;

  8  BEGIN

  9    OPEN c_tool_list;

 10    FETCH c_tool_list BULK COLLECT INTO l_descr_list;

 11    CLOSE c_tool_list;

 12  END;

 

 13  /

PL/SQL procedure successfully completed

Executed in 1.638 seconds

case3：自动批量收集优化举例

SQL> PURGE RECYCLEBIN; --;--清空当前用户的回收站

Done

SQL> alter system flush buffer_cache;  ---清空缓存

System altered

SQL> ALTER SESSION SET plsql_optimize_level=2;

Session altered

SQL> SET timing ON --显示时间

SQL> BEGIN

  2      FOR i IN(

  3        SELECT descr d3

  4        FROM hardware)

  5      LOOP

  6        NULL;

  7      END LOOP;

  8  END;

  9 

 

 10  /

PL/SQL procedure successfully completed

Executed in 1.498 seconds

结论：从case1与case2运行效率可知，只是通过减少从数据库提取数据的次数，就使它的速度快了8.86倍。case3使用自动批量收集优化提取1000000行，它的性能和使用完全批量收集一样好，甚至更优。

（2）三种数据集合类型的批量操作响应时间对比

    测试代码如下：

SQL> alter system flush buffer_cache;  ---清空缓存

System altered

SQL> SET timing ON --显示时间

SQL> DECLARE

  2    TYPE t_va IS VARRAY(1000) OF NUMBER;

  3    TYPE t_nt IS TABLE OF NUMBER;

  4    TYPE t_aa IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

  5 

  6    va t_va;

  7    nt t_nt;

  8    aa t_aa;

  9  BEGIN

 10    FOR i IN 1 ..10000 LOOP

 11      SELECT ROWNUM

 12      -- 在想要的测试集上解除注释

 13      BULK COLLECT INTO va

 14      --BULK COLLECT INTO nt

 15      --BULK COLLECT INTO aa

 16      FROM dual

 17      CONNECT BY LEVEL <=1000;

 18    END LOOP;

 19  END;

 

 20  /

PL/SQL procedure successfully completed

Executed in 11.779 seconds

 

其它两种情形（代码替换为：BULK COLLECT INTO nt和BULK COLLECT INTO aa），输出分别为：

PL/SQL procedure successfully completed

Executed in 11.856 seconds

以及

PL/SQL procedure successfully completed

Executed in 11.794 seconds

    由此可见，对于批量收集的性能来说，3中类型的集合性能比较接近。

3 监控批量收集的开销

批量收集对数据库会话有很显著的影响，数据处理是把取出的数据放在一个缓冲区中，缓冲器在会话的内存中。PL/SQL集合将消耗内存，如果数据库有大的集合或有大量的并发会话，PL/SQL内存消耗的控制就变得很有必要。

以下例子，将定义一个包含不同的获取尺寸（fetch size）的数组，它将用于从hardware表中提取一组行。在一次迭代中，将每次获取5行，然后是10行，50行等等。代码及运行结果如下：

SQL> SET serverout ON

SQL> DECLARE

  2    TYPE t_row_list IS TABLE OF hardware.descr%TYPE;

  3    l_rows t_row_list;

  4 

  5    l_pga_ceiling NUMBER(10);

  6 

  7    TYPE t_fetch_size IS TABLE OF PLS_INTEGER;

  8    l_fetch_sizes t_fetch_size := t_fetch_size(5,10,100,500,1000,10000,100000,1000000);

  9    rc SYS_REFCURSOR;

 10  BEGIN

 11    SELECT VALUE

 12    INTO l_pga_ceiling

 13    FROM v$mystat m, v$statname s

 14    WHERE s.STATISTIC#=m.STATISTIC#

 15    AND s.NAME='session pga memory max';

 16 

 17    dbms_output.put_line('Initial PGA: '||l_pga_ceiling);

 18    FOR i IN 1..l_fetch_sizes.count

 19      LOOP

 20        OPEN rc FOR SELECT descr FROM hardware;

 21        LOOP

 22          FETCH rc BULK COLLECT INTO l_rows LIMIT l_fetch_sizes(i);

 23          EXIT WHEN rc%NOTFOUND;

 24        END LOOP;

 25        CLOSE rc;

 26 

 27        SELECT VALUE

 28        INTO l_pga_ceiling

 29        FROM v$mystat m, v$statname s

 30        WHERE s.STATISTIC#=m.STATISTIC#

 31        AND s.name='session pga memory max';

 32 

 33        dbms_output.put_line('Fetch size: '||l_fetch_sizes(i));

 34        dbms_output.put_line('- PGA Max: '||l_pga_ceiling);

 35      END LOOP;

 36   END;

 37 

 38  /

Initial PGA: 3060088

Fetch size: 5

- PGA Max: 3191160

Fetch size: 10

- PGA Max: 3191160

Fetch size: 100

- PGA Max: 3191160

Fetch size: 500

- PGA Max: 3191160

Fetch size: 1000

- PGA Max: 3191160

Fetch size: 10000

- PGA Max: 4174200

Fetch size: 100000

- PGA Max: 14528888

Fetch size: 1000000

- PGA Max: 118403448

PL/SQL procedure successfully completed

Executed in 10.623 seconds

从输出结果可以看出，获取尺寸超过100000行，PGA消耗从2.9MB增长到112.9MB（在一次fetch调用中批量收集整个1000000行的集合）。如果有数百个会话，每个会话都消耗数百兆，那么肯定对可扩展性是一种威胁。因此，使用批量操作时，在性能和内存消耗之间取得平衡是必需的，建议在不知道未来的结果集的大小（或近似大小）时，不要对结果集发出没有带Limit子句的fetch bulk collect语句。

4 总结

通过本文的程序算例，可以看到批量SQL操作带来的大的性能优势。而从以“行”为中心获取数据转变为以“集合”为中心的批量获取数据，所需的工作量也很小。只需从如下两方面着手：

n  如果是使用了for cursor_variable in（查询或游标）的语法，那么实现批量收集，只需使用较新版的oracle（oracle10g以上）即可，并保证其PL/SQL编译设置是按默认值设置的。

n  否则，也只是一些简单的重新编码问题，将fetch cursor into风格改变为fetch cursor bulk collect into即可。只需增加几个关键字和一些PL/SQL的类型定义，就可以实现代码的改写。

所以，这种转变的工作量小、风险低，但带来的效率提升是大的。这种以集合为中心的想法，在Oracle技能集的其它领域均有体现，如：管道表函数等大规模数据处理。通常情形为使用SQL会比过程性逻辑得到更多性能优势，利用批量操作可以带来效率的大幅提升。