| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: URGENT help- needed (deletes)
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.
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
>
Regards
Rajagopal Venkataramany
 |
 |