Deleting records takes long time and unltimately servers hang

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.