更新优化问题

Question

小弟,写了一个存储过程 ,先从一张表里,作查询(一个带有分组的统计查询),然后再根据这个查询的结果,再来更新这张表,现在数据库的这张表里面只有一万条数据,而那个过程执行的效率底的要气死人,想问有什么好的办法

Answer 1

PROCEDURE P_ADJUSTZONE
   ( p_month IN T_DELLZ_BREAKFIX.belongmonth%type)
   IS
   p_zonecharge_z number(10);
   p_ENGINEERID  varchar2(30);
   p_COMPLETEDATE date;
   p_ZONE varchar2(30);
--
-- To modify this template, edit file PROC.TXT in TEMPLATE 
-- directory of SQL Navigator
--
-- Purpose: Briefly explain the functionality of the procedure
--
-- MODIFICATION HISTORY
-- Person      Date    Comments
-- ---------   ------  -------------------------------------------       
   cursor cur
   is
   select max(ZONECHARGE_Z) zonecharge_z,ENGINEERID,COMPLETEDATE,max(ZONE) ZONE  from T_DELLZ_BREAKFIX where STATUS='0' and belongmonth=p_month group by(ENGINEERID,COMPLETEDATE);
   -- Declare program variables as shown above
BEGIN
    open cur;
    loop
    fetch cur
    into p_zonecharge_z,p_ENGINEERID,p_COMPLETEDATE, p_ZONE;
    update T_DELLZ_BREAKFIX set ZONECHARGEADJUST_Z=p_zonecharge_z where ENGINEERID=p_ENGINEERID and COMPLETEDATE=p_COMPLETEDATE and ZONE=p_ZONE and belongmonth=p_month and STATUS='0';
    exit when cur%notfound;
    end loop;
    close cur;
END; -- Procedure

Answer 2

这个程序很简单，不应该很慢，是不是cursor读取比较慢？试着手工执行一条记录看看？

Answer 3

第一: 好好看看 for .. in 和 forall .. in 两种循环的区别.  (你肯定没用forall .. in).
第二: 检查一下select语句, 是不是可以做到优化~ (至少做到先缩小结果集, 再做笛卡儿积).

Answer 4

再参考下oracle对于for .. in 和 for .. in的解释原话吧, 我不喜欢直接把结果告诉别人, 楼主请好好体会下:Tuning PL/SQL Performance with Bulk Binds
When SQL statements execute inside a loop using collection elements as bind
variables, context switching between the PL/SQL and SQL engines can slow down
execution. For example, the following UPDATE statement is sent to the SQL engine
with each iteration of the FOR loop:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70, ...); -- department numbers
BEGIN
...
FOR i IN depts.FIRST..depts.LAST LOOP
...
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
END LOOP;
END;
In such cases, if the SQL statement affects four or more database rows, the use of
bulk binds can improve performance considerably. For example, the following
UPDATE statement is sent to the SQL engine just once, with the entire nested table:
FORALL i IN depts.FIRST..depts.LAST
UPDATE emp SET sal = sal * 1.10 WHERE deptno = depts(i);
To maximize performance, rewrite your programs as follows:
n If an INSERT, UPDATE, or DELETE statement executes inside a loop and
references collection elements, move it into a FORALL statement.
n If a SELECT INTO, FETCH INTO, or RETURNING INTO clause references a
collection, incorporate the BULK COLLECT clause.
n If possible, use host arrays to pass collections back and forth between your
programs and the database server.
n If the failure of a DML operation on a particular row is not a serious problem,
include the keywords SAVE EXCEPTIONS in the FORALL statement and report
or clean up the errors in a subsequent loop using the %BULK_EXCEPTIONS
attribute.
These are not a trivial tasks. They require careful analysis of program control-flows
and dependencies.