URGENT help- needed (deletes)

From: salu Ullah <salu_ullah_at_hotmail.com>
Date: Tue, 05 Dec 2000 19:16:36 -0000
Message-Id: <10701.123706@fatcity.com>

Hello all

Iam tring to delete rows (million or more) from a table which currently has around 45 million rows. The primary key is composed of (mem_id, join_date, rt_code, rm_code, month). The values in the where clause are used from another temp table which has only three column (mem_id, rt_code, rm_code)similar to main table. As for join_date column value has to match >= 'given_date' (DD-MON-YY format ). I have created a cursor which read mem_id, rt_code, rm_code from temp table & deletes from main table. Somehow its not deleting any rows even after showing procedure successfullt completed or just hangs & do nothing.
Would really appreciate any help.
below is my cursor:

declare
  cursor c_mem is
  select mem_id, rt_code, rm_code
  from temp_rc;

  v_count number(10) :=0;
  v_deletes number(10) :=0;
  v_join_date mem_check.join_date%type :='01-JAN-01';

begin

   for v_memdata in c_mem loop
   v_count := v_count + 1;
   v_deletes := v_deletes + 1;
   delete from mem_check
   where mem_id = v_memdata.mem_id
   and rt_code = v_memdata.rt_code
   and rm_code = v_memdata.rm_code
   and join_date >= v_join_date;
   if v_count = 500 then

      commit;
      v_count := 0;

   end if;
end loop;
commit;
DBMS_OUTPUT.put_line ('Total Rows Deleted from Member Check: '||v_deletes); end;

Thanks

salu

---

Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com Received on Tue Dec 05 2000 - 13:16:36 CST