ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于Update语句的调整(6-2)

关于Update语句的调整(6-2)

原创 Linux操作系统 作者:husthxd 时间:2005-03-17 00:00:00 0 删除 编辑

调整方法三:使用存储过程的批量更新(2)


回到本例,我们使用两种方法来更新数据。实验如下:

 

-- 一次10000行批量提交.

-- 需要在待更新的表上设置标志字段,如本例中的flag

SQL> CREATE OR REPLACE PROCEDURE Sp_Test_Update_1

  2  IS

  3     TYPE t_id IS TABLE OF CHAR (10);

  4 

  5     TYPE t_col1 IS TABLE OF NUMBER (20, 2);

  6 

  7     v_id                          t_id;

  8     v_col1                        t_col1;

  9     v_rowcount                    NUMBER;

 10 

 11     CURSOR c_1

 12     IS

 13        SELECT                                             /* +use_hash(pa p)*/

 14               PA.ID,

 15                 PA.col1

 16               * C.col1

 17        FROM   PA,

 18               P,

 19               C

 20        WHERE  P.ID = PA.ID

 21        AND    NVL (flag, '0') = '0'

 22        AND    P.cid = C.cid

 23        AND    PA.ID NOT IN (SELECT /* +hash_aj*/ ID

 24                             FROM   D)

 25        AND    ROWNUM <= 10000;

 26  BEGIN

 27     LOOP

 28        OPEN c_1;

 29 

 30        FETCH c_1

 31        BULK COLLECT INTO v_id,

 32               v_col1;

 33 

 34        v_rowcount                 := c_1%ROWCOUNT;

 35 

 36        CLOSE c_1;

 37 

 38        EXIT WHEN v_rowcount = 0;

 39        FORALL v_i IN 1 .. v_rowcount

 40           UPDATE PA

 41           SET col1 = v_col1 (v_i),

 42               flag = '1'

 43           WHERE  ID = v_id (v_i);

 44        COMMIT;

 45     END LOOP;

 46  END;

 47  /

 

过程已创建。

 

已用时间:  00: 00: 00.02

SQL>

SQL> exec sp_test_update_1;

 

PL/SQL 过程已成功完成。

 

已用时间:  00: 02: 05.08

SQL>

SQL> CREATE OR REPLACE PROCEDURE sp_test_update_2

  2  IS

  3     TYPE t_rowid IS TABLE OF VARCHAR2 (20);

  4 

  5     TYPE t_col1 IS TABLE OF NUMBER (20, 2);

  6 

  7     v_rowid                       t_rowid;

  8     v_col1                        t_col1;

  9     v_rowcount                    NUMBER;

 10 

 11     CURSOR c_1

 12     IS

 13        SELECT                                             /* +use_hash(pa p)*/

 14               PA.ROWID,

 15                 PA.col1

 16               * C.col1

 17        FROM   PA,

 18               P,

 19               C

 20        WHERE  P.ID = PA.ID

 21        AND    P.cid = C.cid

 22        AND    PA.ID NOT IN (SELECT /* +hash_aj*/ ID

 23                             FROM   D);

 24  BEGIN

 25     OPEN c_1;

 26 

 27     FETCH c_1

 28     BULK COLLECT INTO v_rowid,

 29            v_col1;

 30 

 31     v_rowcount                 := c_1%ROWCOUNT;

 32 

 33     CLOSE c_1;

 34 

 35     FORALL v_i IN 1 .. v_rowcount

 36        UPDATE PA

 37        SET col1 = v_col1 (v_i)

 38        WHERE  ROWID = v_rowid (v_i);

 39     COMMIT;

 40  END;

 41  /

 

过程已创建。

 

已用时间:  00: 00: 00.00

SQL>

SQL> exec sp_test_update_2;

 

PL/SQL 过程已成功完成。

 

已用时间:  00: 00: 23.04

SQL>

 

这里有两种不同的方法,第一种方法分段提取、更新,通过在待更新的表上设置额外的标志字段(flag),每次批量更新一部分数据,由于每次都需要更新标志位,性能不高,过程的执行时间为00: 02: 05.08;第二种方法一次性提取、一次性更新,通过bulk collect一次性把需要更新表的rowid以及更新后的结果载入到服务器内存中,然后用forall子句执行批量更新,执行时间为00: 00: 23.04,略为优于调整前的update语句。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-21738/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1398
  • 访问量
    3842783