[oracle]调整中批量更新的 PLSQL 极限参数

标签: Oracle
发布时间: 2017/4/15 0:00:23
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

在我的表和每个有至少 35 万数据。已列为的以下我想知道我需要看看调整的批量更新脚本 Limit=10000 参数来运行此脚本更好的性能。

DECLARE
    CURSOR rec_cur IS
    SELECT  id,sirname
    FROM table_user;

    TYPE num_tab_t IS TABLE OF NUMBER;
    TYPE vc2_tab_t IS TABLE OF VARCHAR2(15);

    id_tab NUM_TAB_T;
    sirname_tab VC2_TAB_T;
BEGIN
    OPEN rec_cur;
    LOOP
       FETCH rec_cur BULK COLLECT INTO id_tab, sirname_tab LIMIT 10000;
        EXIT WHEN id_tab.COUNT() = 0;
        FORALL i IN id_tab.FIRST .. id_tab.LAST        
           UPDATE table_user_backup
            SET    sirname = sirname_tab(i)
            ,      sirname_date = sysdate
            WHERE  id = id_tab(i);
    END LOOP;
    CLOSE rec_cur;
END;

我测试了三种可能方式的批量更新,分享每个经过的时间。它表明,SQL 是比 pl sql forall 批量更新。更改限制参数范围 1000年-100000 后, 看不到任何区别

-- Bulk update --for 2.5M entries


--Takes 4 Minutes
DECLARE
    CURSOR rec_cur IS
        SELECT  id,name
        FROM table_user;
    BEGIN

       FOR sub in rec_cur
       LOOP
           UPDATE table_user_backup
                SET    name_date = sysdate
               WHERE  id = sub.id;
       END LOOP;

END;


--takes 2.5 minutes
DECLARE
    CURSOR rec_cur IS
    SELECT  id,name
    FROM table_user;

    TYPE num_tab_t IS TABLE OF NUMBER;
    TYPE vc2_tab_t IS TABLE OF VARCHAR2(20);

    id_tab NUM_TAB_T;
    name_tab VC2_TAB_T;
BEGIN
    OPEN rec_cur;
    LOOP
       FETCH rec_cur BULK COLLECT INTO id_tab, name_tab LIMIT 10000;
        EXIT WHEN id_tab.COUNT() = 0;

        FORALL i IN id_tab.FIRST .. id_tab.LAST        
           UPDATE table_user_backup
            SET    name = name_tab(i)
            ,      name_date = sysdate
            WHERE  id = id_tab(i);
    END LOOP;
    CLOSE rec_cur;
END;

--SQL is faster than pl sql
--takes 2.0 minutes
UPDATE table_user_backup tu_backup
   SET (name, name_date) = (SELECT name, sysdate
                                    FROM table_user tu
                                   WHERE tu_backup.id = tu.id)
 WHERE EXISTS( SELECT 1
                 FROM table_user tu
                WHERE tu.id=tu_backup.id);

解决方法 1:

如果这就是实际的脚本,它应该是更有效的得到摆脱的 PL/SQL 处理,只是做在 SQL 中的一切。 批量处理在 PL/SQL 是更有效地比逐行处理在 PL/SQL 但 SQL 是比 PL/SQL 效率更高。

UPDATE table_user_backup tub
   SET (sirname, sirname_date) = (SELECT sirname, sysdate
                                    FROM table_user tu
                                   WHERE tu.id = tub.id)
 WHERE EXISTS( SELECT 1
                 FROM table_user
                WHERE tu.id = tub.id);

如果不是足够的效率,你需要告诉我们哪些索引是可用的查询计划是,多少行都涉及,等等。

官方微信
官方QQ群
31647020