一个大数据量的修改SQL优化问题

1、优化前的SQL，修改需要5小时

CREATE OR REPLACE PROCEDURE UPDATE_UserInfo_AnyField AS
  -- LOCAL VARIABLES HERE
  K VARCHAR2(5);
BEGIN
  -- TEST STATEMENTS HERE
  FOR I IN 1 .. 100 LOOP
   
    K := TO_CHAR(I-1, '00');
   
    UPDATE /*+ nologging */ T_BSS_USERINFO T
          SET T.Validity = NULL
     WHERE T.PARTCOL_USERNO = TO_NUMBER(K);
    COMMIT;
  END LOOP;
END UPDATE_UserInfo_AnyField;
/

 

 

2、优化后的存储过程如下：

CREATE OR REPLACE PROCEDURE Update_Batch_Try AS

 

    TYPE User_RowId IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

    u_tab   User_RowId;

 

    v_BeginTime  VARCHAR2(10);

    v_EndTime    VARCHAR2(10);

    v_TotalTime  NUMBER;

 

 

    v_sum        INTEGER; --计数器

    v_errRecord  NUMBER;  --倒入失败的记录数

   

    err_code     NUMBER;

    err_msg      VARCHAR2(200);

   

    v_Pos        BINARY_INTEGER;

 

    v_StrSql0    VARCHAR2(512);

    v_StrSql1    VARCHAR2(256);

 

    TYPE CURSOR_TYPE IS REF CURSOR;

    UserInfo_Cursor CURSOR_TYPE;

        

BEGIN

 

     v_errRecord := 0;

     v_sum := 0;

    

     SELECT TO_CHAR(SYSTIMESTAMP, 'sssss')

       INTO v_BeginTime

       FROM DUAL;

 

     v_StrSql0 := 'CREATE TABLE T_USERINFO_ROWID NOLOGGING AS

                     SELECT A.RID FROM (

                      SELECT T.ROWID AS RID

                         FROM  T_BSS_USERINFO T

                       WHERE  (T.LEVEL_ID = ''1'' OR T.LEVEL_ID = ''2'')

                       ORDER BY T.ROWID

                      ) A';

     BEGIN                                      

         EXECUTE IMMEDIATE  v_StrSql0;

         

     EXCEPTION      

         WHEN OTHERS THEN

             RETURN;

     END;

    

     v_StrSql1 := 'SELECT T.RID FROM T_USERINFO_ROWID T';

    

     OPEN UserInfo_Cursor FOR v_StrSql1;

    

     LOOP

    

         FETCH UserInfo_Cursor BULK COLLECT INTO u_tab LIMIT 2000; --每次取2000条记录

    

         IF u_tab.COUNT > 0 THEN

    

            BEGIN    

                FORALL i IN 1..u_tab.COUNT SAVE EXCEPTIONS --利用临时表批量导入

                          UPDATE /*+ nologging */ T_BSS_USERINFO T

                             SET T.Validity = NULL

                           WHERE T.ROWID=u_tab(i);

                   

                COMMIT;

               

            EXCEPTION      

                WHEN OTHERS THEN

                    COMMIT;                              

                    v_errRecord := v_errRecord + SQL%BULK_EXCEPTIONS.COUNT;               

                    FOR x IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP

                   

                        v_Pos := SQL%BULK_EXCEPTIONS(x).ERROR_INDEX;

                       

                        err_code := -SQL%BULK_EXCEPTIONS(x).ERROR_CODE;

                                     err_msg := SQLERRM(err_code);

                       

                        DBMS_OUTPUT.PUT_LINE('batch update failure:['||to_char(u_tab(v_Pos))||']:['|| TO_CHAR(err_code) ||']:[' || err_msg || ']');

                       

                    END LOOP;

 

            END;       

               

            v_sum := v_sum + u_tab.COUNT;

    

            u_tab.DELETE;

    

         END IF;

    

         EXIT WHEN UserInfo_Cursor%NOTFOUND;

    

     END LOOP; 

                   

     CLOSE UserInfo_Cursor;

 

     BEGIN                                      

         EXECUTE IMMEDIATE 'DROP TABLE T_USERINFO_ROWID';        

     EXCEPTION      

         WHEN OTHERS THEN

             NULL;

     END;

 

     SELECT TO_CHAR(SYSTIMESTAMP, 'sssss')

       INTO v_EndTime

       FROM DUAL;    

               

     v_TotalTime := TO_NUMBER(v_EndTime) - TO_NUMBER(v_BeginTime);

               

     DBMS_OUTPUT.PUT_LINE('total batch update user info datas===>['||TO_CHAR(v_sum)||']pieces'||'  failure records===>['||TO_CHAR(v_errRecord)||']pieces' || '  total time:[' || TO_CHAR(v_TotalTime)||']seconds!!!');

 

EXCEPTION      

    WHEN OTHERS THEN

        ROLLBACK;

                

        IF UserInfo_Cursor%ISOPEN THEN

           CLOSE UserInfo_Cursor;

        END IF;

 

        IF u_tab.COUNT > 0 THEN

           u_tab.DELETE;

        END IF;

 

        err_code := SQLCODE;

        err_msg := SUBSTR(SQLERRM,1,200);

        DBMS_OUTPUT.PUT_LINE('batch update failure:['||to_char(err_code)||']'||err_msg);

END;

/

 

 

结论、解决方案及效果

       使用上述优化方法可以发现通过对存储过程的优化，可以使Oracle数据库在更新大数据量的效率提高30倍，最根本的优化原理是使用空间来换取时间的做法，充分利用了Oracle的ROWID,批量更新等方法来提高更新大数据量的执行效率。

经验总结、预防措施和规范建议

1,   处理速度加快的原因，是因为使用ORDER  BY  ROWID，按块顺序处理数据，很大程度上减少了物理读，同时使用临时表存储更新记录的ROWID，占有了一定的表空间，所以在使用该优化存储过程之前，需要一定的空闲的表空间。

2,   为了快速生成更新的ROWID临时数据，使用了CTAS（Create Table As Select）的操作方式，如果可以使用NOLOGGING选项，可以进一步提高CTAS操作的执行效率。

3,   为了使用临时表名，该存储过程使用的动态游标（OPEN UserInfo_Cursor FOR v_StrSql1;）。

4,   使用了Oracle数据库的数组，将数据量以FETCH UserInfo_Cursor BULK COLLECT INTO u_tab LIMIT 2000;批量形式读入到数据库的内存，同时使用Bulk Binds(FORALL) 的特性，批量处理，大大降低了主机的资源压力（以2000为单位，批量处理）。