Re: 2nd Req URGENT pls help- needed (deletes)

From: salu Ullah <salu_ullah_at_hotmail.com>
Date: Wed, 06 Dec 2000 19:39:02 -0000
Message-Id: <10702.123828@fatcity.com>

Hello djordjej

I made some changes in my cursor...after running it is says no data found where as the temp table has 7 rows in it. Below is the cursor & the data in the temp table its reading from and also the output of the 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 :='31-DEC-00';

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 sql%notfound then
DBMS_OUTPUT.put_line ('Data not found'); v_deletes := v_deletes;
else
v_deletes := v_deletes + 1;
end if;
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;

RESULT

Data not found
Data not found
Data not found
Data not found
Data not found
Data not found
Data not found
Total Rows Deleted from Rate Check: 0

PL/SQL procedure successfully completed.

DATA in temp_rc

MEM_ID RT_CD RM_CD
------ ----- -----
001640 RACK A
001640 RACK A1D1O
001640 RACK B
001640 RACK C
001640 RACK D
001640 RACK K
001640 RACK L Would really appreciate any help

Thanks

Salman

>From: "djordjej" <djordjej_at_home.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: 2nd Req URGENT help- needed (deletes)
>Date: Tue, 05 Dec 2000 20:15:29 -0800
>
>Hi,
>
>Is this one big delete ? How do you know that it is not doing anything.
>Have you checked v$sesstat and v$rollstat ? It might be deleting records,
>but of course you cannot see this from another session. And deleting 1M
>rows from a 45M rows table, especially if you have foreign keys from child
>tables with no index on the other end, can take quite some time.
>
>Djordje
>
>----- Original Message -----
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Sent: Tuesday, December 05, 2000 10:00 PM
>
>
> > 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 it 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
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: salu Ullah
> > INET: salu_ullah_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> >
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: djordjej
> INET: djordjej_at_home.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Received on Wed Dec 06 2000 - 13:39:02 CST