plsql优化

from tom

bad plsql

 FOR increc IN 
 (SELECT * 
  FROM big_table
  ORDER BY many columns) 
LOOP 
      . . . much procedural code goes here. . . .
   UPDATE big_table
SET … 
WHERE primary_key = inrec.primary_key;
   COMMIT; 
END LOOP; 

good plsql

declare

cursor c is 
select rowid rid, t.* 
  from big_table t
 order by many columns;

l_limit number := 1000;

begin

loop
    -- l_arrayN is a plsql index by table corresponding 
    -- to your columnN - declare of the type of columnN
    fetch c bulk collect 
     into L_ROWID_ARRAY, 
          l_array1, l_array2, 
          l_array3 ...
    LIMIT l_limit;

    for i in 1 .. l_rowid_array.count
    loop
        -- the current body of your loop goes here, but 
        -- instead of an UPDATE, you would assign the 
        -- values to more arrays (the set columns of your 
        -- current update), eg:

        vrep_array(i) := vrep;
        vpaid_array(i) := vpaid;
        ... and so on ...
    end loop;

    forall i in 1 .. l_rowid_array.count
        update big_table
           set reported = v_rep_array(i), 
               paid = v_paid_array, 
               ...
         where rowid = l_rowid_array(i);

    exit when c%notfound;
end loop;

bad plsql:

create or replace procedure slow_by_slow
as
begin
    for x in (select rowid rid, object_name
                from t t_slow_by_slow)
    loop
        x.object_name := substr(x.object_name,2)
                      ||substr(x.object_name,1,1);
        update t 
           set object_name = x.object_name
         where rowid = x.rid;
    end loop;
end;

 

good plsql:

create or replace procedure bulk
as
    type ridArray is table of rowid;
    type onameArray is table 
            of object_name%type;

    cursor c is select rowid rid, object_name
                  from t t_bulk;
    l_rids     ridArray;
    l_onames   onameArray;
    N          number := 100;
begin
    open c;
    loop
        fetch c bulk collect 
        into l_rids, l_onames limit N;
        for i in 1 .. l_rids.count
        loop
           l_onames(i) := substr(l_onames(i),2)
                       ||substr(l_onames(i),1,1);
        end loop;
        forall i in 1 .. l_rids.count
           update t 
              set object_name = l_onames(i) 
            where rowid = l_rids(i);
        exit when c%notfound;
    end loop;
    close c;
end;