开发学院数据库Oracle 批量绑定(bulk binds):FOR循环与FORALL的性能比较... 阅读

批量绑定(bulk binds):FOR循环与FORALL的性能比较

 2008-12-17 13:06:20 来源:WEB开发网   
核心提示:通常在SQL语句中给PL/SQL变量赋值叫做绑定(Binding),一次绑定一个完整的集合称为批量绑定(Bulk Binding),批量绑定(Bulk binds)可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.批量绑定(Bulk binds)包括:(i) Inp

通常在SQL语句中给PL/SQL变量赋值叫做绑定(Binding),一次绑定一个完整的集合称为批量绑定(Bulk Binding)。

批量绑定(Bulk binds)可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

批量绑定(Bulk binds)包括:

(i) Input collections, use the FORALL statement,一般用来改善DML(INSERT、UPDATE和DELETE) 操作的性能

(ii) Output collections, use BULK COLLECT clause,一般用来提高查询(SELECT)的性能

FORALL的语法如下:

FORALL index IN lower_bound..upper_bound sql_statement;

下面是一个简单测试,用以说明FORALL与FOR循环的性能差异。

  SQL> drop table blktest;
  Table dropped.
  Elapsed: 00:00:00.13
  SQL>
  SQL> CREATE TABLE blktest (num NUMBER(20), name varchar2(50));
  Table created.
  Elapsed: 00:00:00.08
  SQL>
  SQL> CREATE OR REPLACE PROCEDURE bulktest
   2 IS
   3   TYPE numtab IS TABLE OF NUMBER (20)
   4    INDEX BY BINARY_INTEGER;
   5
   6   TYPE nametab IS TABLE OF VARCHAR2 (50)
   7    INDEX BY BINARY_INTEGER;
   8
   9   pnums  numtab;
   10   pnames  nametab;
   11   t1    NUMBER;
   12   t2    NUMBER;
   13   t3    NUMBER;
   14 BEGIN
   15   FOR j IN 1 .. 1000000
   16   LOOP
   17    pnums (j)     := j;
   18    pnames (j)    := 'Seq No. ' || TO_CHAR (j);
   19   END LOOP;
   20
   21   SELECT DBMS_UTILITY.get_time
   22    INTO t1
   23    FROM DUAL;
   24
   25   FOR i IN 1 .. 1000000
   26   LOOP
   27    INSERT INTO blktest
   28       VALUES (pnums (i), pnames (i));
   29   END LOOP;
   30
   31   SELECT DBMS_UTILITY.get_time
   32    INTO t2
   33    FROM DUAL;
   34
   35   FORALL i IN 1 .. 1000000
   36    INSERT INTO blktest
   37       VALUES (pnums (i), pnames (i));
   38
   39   SELECT DBMS_UTILITY.get_time
   40    INTO t3
   41    FROM DUAL;
   42
   43   DBMS_OUTPUT.put_line ('Execution Time (hsecs)');
   44   DBMS_OUTPUT.put_line ('---------------------');
   45   DBMS_OUTPUT.put_line ('FOR loop: ' || TO_CHAR (t2 - t1));
   46   DBMS_OUTPUT.put_line ('FORALL:  ' || TO_CHAR (t3 - t2));
   47 END;
   48 /
  Procedure created.
  Elapsed: 00:00:01.46
  SQL> exec bulktest;
  Execution Time (hsecs)
  ---------------------
  FOR loop: 30361
  FORALL:  4792
  PL/SQL procedure successfully completed.
  Elapsed: 00:06:32.92

我们可以看到FORALL较FOR循环性能大大提高。

Tags:批量 绑定 bulk

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接