Deleting records takes long time and unltimately servers hang

[CoolGuyz]

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!!!

[davey23uk]

trace it - see where your time is being taken

[CoolGuyz]

Can you please suggest how can I trace it?

[gtcol]

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.

[davey23uk]

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

[gandolf989]

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.

[sunil_kandi]

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

[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.

[PAVB]

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

What else should we know?

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

[CoolGuyz]

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