Everybody knows: TRUNCATE is fast, because it's a DDL, and it directly resets the highwater mark, and writes nothing in the rollback segments.
So TRUNCATE is much faster than DELETE FROM.
I just tried to measure time difference on my laptop, using SET TIMING ON, manipulating data in a table containing 200'000 records.
13:39:48 SQL> create table my_table
(id number primary key,
text varchar2(20)
);
Table created.
Elapsed: 00:00:00.03
13:40:48 SQL> begin
13:41:01 2 for i in 1 .. 200000 loop
13:41:01 3 insert into my_table (id, text) values(i, 'text' || i);
13:41:01 4 end loop;
13:41:01 5 end;
13:41:01 6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:28.48
13:41:29 SQL> commit;
Commit complete.
Elapsed: 00:00:00.03
13:41:41 SQL> truncate table my_table;
Table truncated.
Elapsed: 00:00:00.48
13:41:58 SQL> begin
13:42:14 2 for i in 1 .. 200000 loop
13:42:14 3 insert into my_table (id, text) values(i, 'text' || i);
13:42:14 4 end loop;
13:42:14 5 end;
13:42:14 6 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:21.41
13:42:37 SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
13:42:40 SQL> delete from my_table;
200000 rows deleted.
Elapsed: 00:00:08.63
13:42:55 SQL> commit;
Bottom line : TRUNCATE is immediate, DELETE runs during 8 seconds.
