Reducing Loop Overhead for DML Statements and Queries with Bulk SQL(10gr2)

PL/SQL运行SQL的机制是把SQL语句，比如DML,SELECT，发送给SQL 引擎。然后SQL引擎把SQL语句的结果返回给PL/SQL。想象一下，下面这样的PL/SQL块。

FOR j IN 1..99999999 LOOP
  VAR1[i]=i;
  INSERT INTO TEST VALUES(VAR[i]);
END LOOP;

这个PL/SQL块非常简洁明了，它循环9999999次，每一次循环都把 i 赋值到 VAR1[i]中，然后把这个VAR1[ i ] insert到表TEST中。这么简单的SQL却有着非常严重的性能问题。因为他要循环99999999次，每一次循环PL/SQL引擎都像SQL引擎发送一条INSERT 语句，这期间的通讯开销会导致运行很慢。 Oracle对这种现象的解决方案是使用批量SQL,比如，FORALL，BULK COLLECT。

首先，我们看一下FORALL。这里我们分为2部分讲，

普通的FORALL
INDICES OF 和 VALUES OF

普通的FORALL

这里我们做一个比较，先是用普通的FOR把一万条数据插入一个表A中，记录一下时间。然后把相同的一万条数据插入同一个表A中，但这次用FORALL。我们看一下哪个快。

 1 CREATE OR REPLACE PROCEDURE for_vs_forall
 2 AS
 3   timer1 INTEGER;
 4   timer2 INTEGER;
 5   
 6   TYPE num_tab_type IS TABLE OF NUMBER INDEX BY  binary_integer ;
 7   num_table num_tab_type;
 8 BEGIN
 9   /*初始化 num_table*/
10   FOR i IN 1..9999 LOOP
11     num_table(i) := i;
12   END LOOP;
13   
14   /*在FOR循环里向A中insert数据，并在循环开始前和循环结束后记录时间，算出循环一共花的时间*/
15   
16   timer1 := DBMS_UTILITY.get_time;
17   FOR i IN 1..9999 LOOP
18     num_table(i) := i;
19     insert into A values(num_table(i));
20   END LOOP;
21   timer2 := DBMS_UTILITY.get_time;
22   DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
23   DBMS_OUTPUT.PUT_LINE('FOR loop: ' || TO_CHAR((timer2 - timer1)/100));
24   
25   
26   /*清空A表*/
27   
28   execute immediate 'delete from A';
29   
30   
31   /*在FORALL循环里向A中insert数据，并在循环开始前和循环结束后记录时间，算出循环一共花的时间*/
32   
33   timer1 := DBMS_UTILITY.get_time;
34   FORALL i IN 1..9999 
35     INSERT INTO A VALUES(num_table(i));
36   timer2 := DBMS_UTILITY.get_time;
37   DBMS_OUTPUT.PUT_LINE('Execution Time (secs)');
38   DBMS_OUTPUT.PUT_LINE('FORALL loop: ' || TO_CHAR((timer2 - timer1)/100));
39   
40 END;
41

简单讲解一下上面的测试，9-10行是初始化num_table这个集合。这里有个小问题，就是如果我在声明这个集合的时候没有指定指定INDEX BY BINARY_INTEGER，那么这里就会报一个ORA-06531: Reference to uninitialized collection的错误。这个需要研究一下，但我们先跳过这里。 16-23行是通过FOR循环往A表中insert数据，并且在循环开始和循环结束都记录时间以便算出FOR循环的时间。31-38通过FORALL insert数据。在循环开始前和循环结束后都记录并计算时间。最后看一下两种循环哪个更快。

1 SQL> exec for_vs_forall
2 Execution Time (secs)
3 FOR loop: 2.95
4 Execution Time (secs)
5 FORALL loop: .05
6 
7 PL/SQL procedure successfully completed.

运行一下，结果如上所示，FORALL非常快。

INDICES OF 和 VALUES OF

/*首先创建三个表，
valid_orders 记录了customer name 以及对应的 order数量
big_orders  记录了order 数量比较大的customer name 以及order数量
rejected_orders 如果有不符合标准的数据，比如 order 数量为NULL 或者0，则把 customer name 和order数量记录进这个表*/
CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2));
CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0;
CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0;


CREATE OR REPLACE PROCEDURE test_forall
AS
  /*创建两个集合用来存储一系列的customer name 和 order amout 这两个集合类似于数组，相同下表的
  两个元素就组成了一对儿数据对儿，可以存进 valid_order这个表中。 比如 cust_tab(1), amount_tab（1）*/
  SUBTYPE cust_name IS valid_orders.cust_name%TYPE;
  TYPE cust_typ IS TABLE OF cust_name;
  cust_tab cust_typ;
  
  SUBTYPE order_amount IS valid_orders.amount%TYPE;
  TYPE amount_typ IS TABLE OF order_amount;
  amount_tab amount_typ;
  
  /*创建两个数据结构，
  big_order_tab 这个结构里面每一个元素对应着一对customer name和order amount的组合，
  如果里面存着（8,10）那么就意味着第8，10对儿 customer name，order amount应该存进 big_order表
  regected_order_tab记录了应该存进rejected_order的数据对儿*/ 
  TYPE index_pointer_t IS TABLE OF PLS_INTEGER;
  big_order_tab index_pointer_t := index_pointer_t();
  rejected_order_tab index_pointer_t := index_pointer_t();
  
  /*初始化cust_tab 和 amount_tab 生成一些  customer name 和 order amount的数据对儿*/
  PROCEDURE setup_data IS BEGIN 
    cust_tab := cust_typ('Company1','Company2','Company3','Company4','Company5');
    amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL);
  END;
  
BEGIN
  
  /*调用setup_data来初始化，生成一些 customer name 和 order amount*/
  setup_data();
  
  /*把这些数据打印出来*/
  DBMS_OUTPUT.PUT_LINE('--- Original order data ---');
  FOR i IN 1..cust_tab.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||amount_tab(i));
  END LOOP;
  
  /*如果有一些记录不符合标准，比如order amount=0 或者 null，就把对应的数据对儿删掉。*/
  FOR i IN 1..cust_tab.LAST LOOP
    IF amount_tab(i) is null or amount_tab(i) = 0 THEN
      cust_tab.delete(i);
      amount_tab.delete(i);
    END IF;
  END LOOP;
  
  /*输出一下现在的数据， 因为有些数据被删除了，所以输出之前需要用 
  IF cust_tab.EXISTS（i）确定数据是否存*/
  DBMS_OUTPUT.PUT_LINE('--- Data with invalid orders deleted ---');
  FOR i IN 1..cust_tab.LAST LOOP
    IF cust_tab.EXISTS(i) THEN
      DBMS_OUTPUT.PUT_LINE('Customer #' || i || ', ' || cust_tab(i) || ': $' ||amount_tab(i));
    END IF;
  END LOOP;
  
  /*注意这里才是重点，因为cust_tab里有一些数据被删除了，这时候如果你还用
  FORALL i IN cust_tab.FIRST .. cust_tab.LAST
  或者
  FORALL i IN 1 .. count
  这两种形式，就会出错。 会碰到ORA-22160: element at index [2] does not exist这种错误，
  所以需要下面的这种执行方式。
  */
  FORALL i IN INDICES OF cust_tab
    INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i));
    

  /*下面重新初始化数据，学习一下 values of*/
  setup_data();
  
  /*这里遍历一下cust_tab 以及 amount_tab这两个表，如果甄别数据，把对应的数据放入big_order 
  或者rejected_order*/
  FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
    IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN
      rejected_order_tab.EXTEND;
      rejected_order_tab(rejected_order_tab.LAST) := i;
    END IF;
    IF amount_tab(i) > 2000 THEN
      big_order_tab.EXTEND;
      big_order_tab(big_order_tab.LAST) := i;
    END IF;
  END LOOP;
  
  
  /*这里要注意一个前提就是 big_order_tab中存储的内容是指向cust_tab和order_tab的指针，所以
  如果你还像以前这样写
  FORALL i IN  big_order_tab.FIRST .. big_order_tab.LAST
    INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
  那么你插入的数据就错了。因为这时候的i是 big_order_tab的下标而不是内容，你需要这样写 
  */
  FORALL i IN VALUES OF rejected_order_tab
    INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i));
  FORALL i IN VALUES OF big_order_tab
    INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i));
  COMMIT;
  
END;

运行一下

SQL> delete from valid_orders;

0 rows deleted.

SQL> delete from big_orders;

4 rows deleted.

SQL> delete from rejected_orders;

0 rows deleted.

SQL> commit;

Commit complete.


SQL> EXEC test_forall
--- Original order data ---
Customer #1, Company1: $5000.01
Customer #2, Company2: $0
Customer #3, Company3: $150.25
Customer #4, Company4: $4000
Customer #5, Company5: $
--- Data with invalid orders deleted ---
Customer #1, Company1: $5000.01
Customer #3, Company3: $150.25
Customer #4, Company4: $4000

PL/SQL procedure successfully completed.

SQL> select * from valid_orders;

CUST_NAME                                                                                            AMOUNT
------------------------------------------------------------------------------------------------ ----------
Company1                                                                                            5000.01
Company3                                                                                             150.25
Company4                                                                                               4000

SQL> select * from big_orders;

CUST_NAME                                                                                            AMOUNT
------------------------------------------------------------------------------------------------ ----------
Company1                                                                                            5000.01
Company4                                                                                               4000

SQL> select * from rejected_orders;

CUST_NAME                                                                                            AMOUNT
------------------------------------------------------------------------------------------------ ----------
Company2                                                                                                  0
Company5

posted on 2013-04-25 11:33 kramer 阅读(243) 评论(0) 收藏举报

刷新页面返回顶部

kramer

Reducing Loop Overhead for DML Statements and Queries with Bulk SQL(10gr2)

普通的FORALL

INDICES OF 和 VALUES OF

导航

公告