Deleting records takes long time and unltimately servers hang

We have a simple query to delete huge records (Keeping only records from last two days) from database and it runs during night time. But it takes around 45 mins and by that time transactions just wait for data and servers utilization go high.

delete from table where date < sysdate-2;

Any suggestions or ulternatives how we can improve the time of this job?
Your help will be greatly appreciated!

Thanks in advance!!!

trace it - see where your time is being taken

Can you please suggest how can I trace it?

Code:

---

Any suggestions or ulternatives how we can improve the time of this job?
Your help will be greatly appreciated!

Thanks in advance!!!

---

If the ratio is like 10:1(ie., i mean very high) or more between what gets deleted and what is left behind, a better approach would be to CTAS what you want to retain and drop/truncate the old table. Rename it to original, if dropped.
If you can't do this, there are lots of ways to improve it; at first, do as Davey said, trace. Analyze it. Diagnose. Tune it. :p:p

ask your dba, they will know how to trace sessions for you

You can also do date range partitioning. Every day would be one partition, and every day you would drop the partition from two days ago. Just be aware that an on delete row level trigger would probably be expensive to maintain.

Consider partitioning the table and alter table drop partition will address your issue

Query is -
DELETE FROM ECOOVERLAYEVALUATION
WHERE creationdate < sysdate - 2;
COMMIT;

This table "ECOOVERLAYEVALUATION" in turn connects to other tables and deletes records from them.

I can not use Truncate as I do want to retain data for past two days.

Quote:

---

Originally Posted by CoolGuyz

Query is -
DELETE FROM ECOOVERLAYEVALUATION
WHERE creationdate < sysdate - 2;
COMMIT;

This table "ECOOVERLAYEVALUATION" in turn connects to other tables and deletes records from them.

I can not use Truncate as I do want to retain data for past two days.

---

So you are cascade deleting? I see, you are adding pieces of information one-at-a-time, some people would get crazy about that :D

What else should we know?

By the way, are you sure your predicate is no like: ... < trunc(sysdate - 2) ?

can we do something like "< trunc(sysdate - 2)'

Quote:

---

Originally Posted by CoolGuyz

can we do something like "< trunc(sysdate - 2)'

---

Code:

---

select trunc(sysdate -2) from dual;

---

Quote:

---

Originally Posted by PAVB

So you are cascade deleting? I see, you are adding pieces of information one-at-a-time, some people would get crazy about that :D

What else should we know?

By the way, are you sure your predicate is no like: ... < trunc(sysdate - 2) ?

---

Can we do something like < trunc(sysdate - 2) ??? :confused:

Quote:

---

Originally Posted by PAVB

Code:

---

select trunc(sysdate -2) from dual;

---

---

How can I change my query using "select trunc(sysdate -2) from dual" ??? :confused:

You can do it in a pl/sql procedure.
Place the delete statement inside the loop. something like
For i in reverse 3..100 Loop
delete from table where date < sysdate-i;
commit;
end loop;

Quote:

---

Originally Posted by malay_biswal

You can do it in a pl/sql procedure.
Place the delete statement inside the loop. something like
For i in reverse 3..100 Loop
delete from table where date < sysdate-i;
commit;
end loop;

---

welcome to ORA-1555

Quote:

---

Originally Posted by CoolGuyz

How can I change my query using "select trunc(sysdate -2) from dual" ??? :confused:

---

So you are suggesting that it would be faster if you used a constant instead of sysdate-2 or trunc(sysdate-1). How do you know that sysdate-2 isn't a constant? :confused: It seems like a constant to me. That is constant as of the time it is being used. I don't see how your suggestion will change anything.

You should go back and create explain plans for every table that gets deleted from when you delete from the main table. You might also try partitioning those tables by day as well. This way you would go around and drop and recreate partitions from all of your tables.

For what you are doing 45 minutes might not be that bad, but dropping a bunch of partitions would be much faster, IMHO.