PL/SQL not as easy as it looks.

From: Kevin O'Gorman <kevin_at_trixie.kosman.via.ayuda.com>
Date: Sun, 26 Dec 1999 10:30:56 -0800
Message-ID: <38665EE0.BD0B51E0@trixie.kosman.via.ayuda.com>

I'm trying my first PL/SQL program, to recode an update to a summary table that was originally three SQL commands. The result is a program that looks right, and works on very small inputs, but runs very slowly and crashes before finishing on real inputs (it fails in extending a rollback segment).

I could sure use some pointers.

I've got a modest sized (10K rows) delta table I'm using to update a large (6M rows) summary table. The SQL had an UPDATE, an INSERT and a DELETE command, and had cache behavior I thought I could improve by making the update/insert/delete decision on a row-by-row basis.

Here's the code, modified to remove substitution variables which just allow me to use different table names. I use two cursors, the outer one (c1) scans the delta. The inner one (cview) looks up the corresponding record in the summary (if one exists) using the fields of its primary key. There will be at most one such record, so that part is not a loop. The code looks clean to my newbie eye, and I really don't know what I did wrong, but it must be something.

The rollback segments are large enough to accomodate a copy of the entire summary table, so I have no clue what I'm doing that would exhaust that resource.

HELP???

---

declare
  deltarec DELTA%ROWTYPE;
  cursor c1 is
    select * from DELTA;

  updates integer;
  deletes integer;
  inserts integer;

begin

  updates := 0;
  deletes := 0;
  inserts := 0;

  for deltarec in c1 loop
    declare

      viewrec SUMMARY%ROWTYPE;
      cursor cview is
        select /*+ FIRST_ROWS */ * from SUMMARY w
        where
          w.w0_supp_nation = deltarec.w0_supp_nation and
          w.w0_cust_nation = deltarec.w0_cust_nation and
          w.w0_shipmonth = deltarec.w0_shipmonth     and
          w.w0_ordermonth = deltarec.w0_ordermonth   and
          w.w0_partkey = deltarec.w0_partkey         and
          w.w0_suppkey = deltarec.w0_suppkey;
    begin
      open cview;
      fetch cview into viewrec;
      if cview%FOUND then
        if viewrec.w0_count = - deltarec.w0_count then
          deletes := deletes + 1;
          delete from SUMMARY w where
            w.w0_supp_nation = deltarec.w0_supp_nation and
            w.w0_cust_nation = deltarec.w0_cust_nation and
            w.w0_shipmonth = deltarec.w0_shipmonth     and
            w.w0_ordermonth = deltarec.w0_ordermonth   and
            w.w0_partkey = deltarec.w0_partkey         and
            w.w0_suppkey = deltarec.w0_suppkey;
        else
          updates := updates + 1;
          update SUMMARY wu
          set
            wu.w0_quantity = wu.w0_quantity + deltarec.w0_quantity,
            wu.w0_volume   = wu.w0_volume   + deltarec.w0_volume,
            wu.w0_cost     = wu.w0_cost     + deltarec.w0_cost,
            wu.w0_count    = wu.w0_count    + deltarec.w0_count;
        end if;
      else
        inserts := inserts + 1;
        insert into SUMMARY 
          (
            w0_supp_nation,
            w0_cust_nation,
            w0_shipmonth,
            w0_ordermonth,
            w0_partkey,
            w0_suppkey,
            w0_quantity,
            w0_volume,
            w0_cost,
            w0_count
          ) values (
            deltarec.w0_supp_nation,
            deltarec.w0_cust_nation,
            deltarec.w0_shipmonth,
            deltarec.w0_ordermonth,
            deltarec.w0_partkey,
            deltarec.w0_suppkey,
            deltarec.w0_quantity,
            deltarec.w0_volume,
            deltarec.w0_cost,
            deltarec.w0_count
          );
      end if;
      close cview;
    end;           -- inner block
  end loop;        -- c1 loop

  delete from counts;
  insert into counts

     values(inserts, deletes, updates);
end;               -- outer block

/   

--
Kevin O'Gorman (805) 650-6274 kogorman_at_pacbell.net At school: kogorman_at_cs.ucsb.edu
Permanent e-mail forwarder: Kevin.O'Gorman.64_at_Alum.Dartmouth.org Received on Sun Dec 26 1999 - 12:30:56 CST