Re: URGENT help- needed (deletes)

Hello Rajagopal

I did add %rowcount in my cursor & still got no deletes. What i added in my cursor is :
if sql%rowcount = 0 THEN
  DBMS_OUTPUT.put_line ('Data not found');   v_deletes := v_deletes;
else
  v_deletes := v_deletes + 1;
end if;

after adding it & running the cursor i got :

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 Mem Check: 0

PL/SQL procedure successfully completed.

Both data has same three columns with same data type. For more info below are the data from tow tables

Temp_rc table

MEM_ID RT_C RM_CODE
------ ---- --------
001640 RACK A
001640 RACK A1D1O
001640 RACK B
001640 RACK C
001640 RACK D
001640 RACK K
001640 RACK L Mem_check table
MEM_ID JOIN_DATE RT_C RM_CODE A

------ --------- ---- -------- -
001640 04-JAN-01 RACK A        B
001640 01-APR-01 RACK L        B
001640 02-APR-01 RACK A        B
001640 03-APR-01 RACK A        B
001640 04-APR-01 RACK D        B
001640 05-APR-01 RACK L        B
001640 22-APR-01 RACK A        B
001640 23-APR-01 RACK B        B
001640 24-APR-01 RACK A1D10    B
001640 26-APR-01 RACK A        B
001640 27-APR-01 RACK A        B
001640 28-APR-01 RACK B        B
001640 29-APR-01 RACK A        B
001640 30-APR-01 RACK D        B

Would appreciate any help.

Thanks

Salman

>From: Rajagopal Venkataramany <rajagopalvr_at_excite.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: URGENT help- needed (deletes)
>Date: Tue, 05 Dec 2000 18:36:05 -0800
>
>Hi,
>
> One of the reason could be that the "join_date" column stores
> the time component along with the date. That may be the reason
> that the delete is failing and the process does a redundant run
> of reading all the rows before it exits out of the program.
>
> If u are sure that the delete statements should always delete
> atleast 1 row, then I suggest u check for sql%rowcount after the
> delete statement. If sql%rowcount = 0 then u can raise a exception
> and abort. This way, you will come to know about the problem much
> earlier rather than waiting till the job completes.
>
>** If this is true then do not use trunc(join_date) as it will still
> not use the index. Let us know so that we can take u to the next
> step after knowing that this was the issue.
>
> Hope this helps...
>
>Regards
>Rajagopal Venkataramany
>
> On Tue, 05 Dec 2000 11:16:55 -0800, ORACLE-L_at_fatcity.com wrote:
>
> > 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
> >
> > --
> > 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).
>
>
>Regards
>Rajagopal Venkataramany
>
>
>
>
>
>_______________________________________________________
>Tired of slow Internet? Get @Home Broadband Internet
>http://www.home.com/xinbox/signup.html
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Rajagopal Venkataramany
> INET: rajagopalvr_at_excite.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 - 17:02:11 CST