When you delete many rows with DELETE FROM, you generate a lot of redo and undo. It may even fail, if your undo runs out of space or your archive location is insufficient.
If you need to empty a full table, it is a poor decision to use DELETE. In that case, use TRUNCATE. It is far more efficient and quicker.
You know it should be quick, but who has the opportunity to test it on a big table?
I had just over 100.000.000 rows in a audit table that had to be cleaned.
Let's see the results (schema and table name have been changed):
SQL> select count(1) from SCOTT.AUDIT ;
COUNT(1)
----------
101622759
Elapsed: 00:02:02.38
SQL> truncate table SCOTT.AUDIT drop storage;
Table truncated.
Elapsed: 00:00:48.69
48 seconds for 101 million rows. Not bad! Drop storage is default BTW.
Times depend on Operating system. This is a pretty quick Linux server.
