| 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: 2nd Req URGENT help- needed (deletes)
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
> 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
>
>
 |
 |