oracle对于大表利用rowid提升update性能

使用rowid能迅速的定位数据，从而提升update效率

 

1.在scott表中创建test表

SQL> create table test as select * from dba_audit_trail;

Table created.

SQL> set timing on;
SQL> select count(*) from test;

  COUNT(*)
----------
   2086439

Elapsed: 00:00:00.73

 

2. order by rowid 对块进行排序，减少块访问的次数

DECLARE
  CURSOR CUR IS
    SELECT ROWID AS ROW_ID FROM TEST ORDER BY ROWID;
  V_COUNTER NUMBER;
BEGIN
  V_COUNTER := 0;
  FOR ROW IN CUR LOOP
    UPDATE TEST SET USERNAME = 'ghsj_jcsj' WHERE ROWID = ROW.ROW_ID;
    V_COUNTER := V_COUNTER + 1;
    IF (V_COUNTER >= 1000) THEN
      COMMIT;
      V_COUNTER := 0;
    END IF;
  END LOOP;
  COMMIT;
END;

 

PL/SQL procedure successfully completed.

Elapsed: 00:06:04.89

 

200多万条记录只花了6分多钟，效率还可以。

 

本文乃原创文章，请勿转载。如须转载请详细标明转载出处