Deleting large amounts of data

[themightyrhino]

Hi,

I'm currently running a delete process based on the results of a sub query but its taking forever to run. I'm new to using Oracle, and am constantly looking for better ways of doing things and wondered if anyone could help......

delete FROM DELTA_OD001
WHERE order_no in
(SELECT Td.order_no
FROM IMPORT_OD001_CU STOMERS CD, DELTA_OD001 TD
WHERE CD.ORANGE_ID = TD.ORANGE_ID);

The volume of rows in Delta_od001 is approx 8.5million and will go up 250k per week. The number of records matched in the sub select if about 950k, and will be relatively constant each week, although will probably drop to about 700k on average.

Do you know of a better way to do this?

[Pilgrim333]

Can you use PL/SQL as well?

[themightyrhino]

Not got massive amounts of experience with it, however im doing this through SQL Developer so dont see why not...

[Pilgrim333]

Then try making a procedure of it.

Make a cursor and loop through that cursor and delete every record you find in that cursor. I think what is making the delete statement so slow, is the in clause you are using.


It should look something like this:

declare
 
cursor c_ord
is
  SELECT Td.order_no
  FROM IMPORT_OD001_CUSTOMERS CD
     , DELTA_OD001 TD
  WHERE CD.ORANGE_ID = TD.ORANGE_ID
;
begin
  for r_ord in c_ord loop
    delete FROM DELTA_OD001
    WHERE order_no = r_ord.order_no;
  end loop;
  commit;
end;
 

If you get a snapshot too old, let me know, then i will tell you how to get around that.

Pilgrim.

[themightyrhino]

Then try making a procedure of it.

Make a cursor and loop through that cursor and delete every record you find in that cursor. I think what is making the delete statement so slow, is the in clause you are using.


It should look something like this:

declare
 
cursor c_ord
is
  SELECT Td.order_no
  FROM IMPORT_OD001_CUSTOMERS CD
     , DELTA_OD001 TD
  WHERE CD.ORANGE_ID = TD.ORANGE_ID
;
begin
  for r_ord in c_ord loop
    delete FROM DELTA_OD001
    WHERE order_no = r_ord.order_no;
  end loop;
  commit;
end;
 

If you get a snapshot too old, let me know, then i will tell you how to get around that.

Pilgrim.

Thanks, will give it a try!

[amitpatel66]

Hi,

I'm currently running a delete process based on the results of a sub query but its taking forever to run. I'm new to using Oracle, and am constantly looking for better ways of doing things and wondered if anyone could help......

delete FROM DELTA_OD001
WHERE order_no in
(SELECT Td.order_no
FROM IMPORT_OD001_CU STOMERS CD, DELTA_OD001 TD
WHERE CD.ORANGE_ID = TD.ORANGE_ID);

The volume of rows in Delta_od001 is approx 8.5million and will go up 250k per week. The number of records matched in the sub select if about 950k, and will be relatively constant each week, although will probably drop to about 700k on average.

Do you know of a better way to do this?

Is this query not working fine?

Always rebuild an index after large deletes for better performance