Question

如何在oracle9i中显著提升更新超大表的性能？

问题概述：有一个表t1约有8百万条记录，需要更新其中一个字段f1的第6,7字节，以及11,12字节，其中f1类型为varchar2(15),测试耗时3个小时，需要求助大侠帮忙指点如何提高update的性能。
测试概述：通过一个循环来操作的，具体用到的语句如下：
BEGIN
v_Num := 0;
select count(*) into v_Num from t1;
LOOP
exit when (v_Num<=0);
---shrink rollback segment ---
EXECUTE IMMEDIATE 'alter rollback segment r01 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r02 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r03 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r04 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r05 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r06 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r07 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r08 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r09 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r010 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r011 shrink';
EXECUTE IMMEDIATE 'alter rollback segment r012 shrink';
---update t1---
UPDATE t1 SET f1 =substr(f1,1,5)||CHR(100)||CHR(254)||substr(f1,8,3)||CHR(2)||CHR(3)||substr(f1,13)
WHERE ROWNUM < 30001;
commit;
v_Num:=v_Num-30000;
END LOOP;
END;
/
commit;
测试平台：在高性能的SUN 小型机+光纤阵列上，IO耗损约8个百分点。

需要求助大侠帮忙指点如何提高update的性能。谢谢！

Answer 1

我感觉你这程序一直在修改前30000行记录。你用rownum它的输出顺序不变啊，如何区分修改和没修改记录？
800万行记录，需要分段提交，还是选一个字段来进行分段修改吧。
例：
UPDATE t1 SET f1 =substr(f1,1,5)||CHR(100)||CHR(254)||substr(f1,8,3)||CHR(2)||CHR(3)||substr(f1,13) WHERE 字段 between 值1 and 值2;
commit;
UPDATE ...
...

追问

我的问题中的语句和实际测试的有点不一样，确实加了一个条件来判断未更新的记录，被测试的语句为：
UPDATE t1 SET f1 =substr(f1,1,5)||CHR(100)||CHR(254)||substr(f1,8,3)||CHR(2)||CHR(3)||substr(f1,13)
WHERE substr(f1,6,2) != '64FE' and ROWNUM < 30001;

测试结果：确实更新了整表全记录，但是耗时3小时，请大侠你帮忙指导下如何提升性能，不胜感激。谢谢！

追答

除了我上面的问题以外，更关键是substr(f1,6,2) != '64FE' 比较上。在处理大量记录时千万不要使用!=关系符，该语句每次都要比较全部记录。800万记录，修改完所有记录需要比较800*800万次。快快去改掉!=比较符吧。