Delete ALL taking too long

Database
Enthusiast

Delete ALL taking too long

DELETE FROM TABLENAME ALL;
this is taking too much time . Running from past 30 mins. Total records are 1420712.
Why?

plz help
4 REPLIES
Enthusiast

Re: Delete ALL taking too long

(A) Check if this session is actually blocked (bug your DBA)

If not (A) then start looking for skew, too much of indexes, triggers (who knows ?) etc .....
Enthusiast

Re: Delete ALL taking too long

Is the delete executing in ANSI transaction mode? If so, the fast delete method is not used. The use of certain table options, such as permanent journals or replication, can also make delete operations slower.
rgs
Enthusiast

Re: Delete ALL taking too long

If this is an ANSI SQL session to force it to use the fast delete method you have to submit the DELETE ALL and COMMIT as one request.

DELETE FROM TABLENAME ALL; COMMIT;
Enthusiast

Re: Delete ALL taking too long

Thanks,

I have bugged my DBA . He did something and now its running fine.

Thanks a lot for ur suggestions.