Truncate versus Delete: a closer Look

Both Delete and Truncate SQL commands are used to remove data from the table.
Delete is a DML command that generates undo (rollback information) (in fact ‘delete’ generates the most amount of Undo of all the DML statements since the whole row has to be copied to the undo segments for rolling back) and hence can be rolled back and much slower.
Truncate is a DDL command that doesn’t generate undo. You cannot rollback a truncate operation and is much faster than a delete, when it comes to cleaning out the table. Truncate basically resets the HWM (ie High water mark) to ‘zero’ (ie start of the segment) without actually deleting the rows, but delete operation doesn’t touch the high water mark. Highwater mark of the table is the highest point (block/extent) the segment has ever contained data and is stored in the segment header.  
Conditional delete (ie filter condition) is not possible with Truncate operation (ie you only can empty the whole table with truncate).  So after a delete operation of all rows, Oracle will still scan all the blocks upto HWM in case of a Full table scan even if they don’t contain any rows.
Demo:
-----
SQL>drop table t;
Table dropped.
-- Lets create a table T with 10000 rows
SQL>create table t as select * from all_objects where rownum <=10000;
Table created.
-- Lets update the statistics of the table to be used by the Cost based optimizer
SQL>analyze table t compute statistics;
Table analyzed.
-- As you see below the table T has a total of 512 data blocks out of which 295 are used blocks and 217 blocks are above HWM(ie empty blocks)
SQL>select table_name, blocks, empty_blocks from user_tables where table_name='T';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 295 217
SQL>select blocks,extents from user_Segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
512 17
Doing a Count (*) & Full table scan yields following results:
Select count(*) from  t;
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0        277          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.02          0        277          0           1
-- Lets time the delete operation
SQL>set timing on
SQL>delete from t;
10000 rows deleted.
Elapsed: 00:00:11.06
Note that DELETE takes about 11 seconds
-- Now, lets find out how much rollback (undo) entries have been generated because of this DML operation
SQL>select r.name "UNDO segment" ,t.status,t.cr_get,t.used_ublk,t.noundo
2 from v$session s,v$transaction t,v$rollname r
3 where t.addr=s.taddr AND
4 t.xidusn=r.usn AND
5 s.audsid=userenv('SESSIONID')
6 /
UNDO segment STATUS CR_GET USED_UBLK NOU
------------------------------ ---------------- ---------- ---------- ---
RB01 ACTIVE 282 566 NO
-- You see about 566 undo blocks are used by this transaction (for rollback, read consistency etc).
-- Also you notice that V$Transaction.NOUNDO = NO in this case, which means this is a transaction that CAN BE ROLLED BACK using undo info.
SQL>commit;
Commit complete.
-- Lets reanalyze the table to get the updated statistics
SQL>analyze table t compute statistics;
Table analyzed.
SQL>set timing off
SQL>select table_name, blocks, empty_blocks from user_tables where table_name='T';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 295 217
SQL>select blocks,extents from user_Segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
512 17
-- AS you see above the DELETE operation doesnt do anything to the Used or Empty blocks of the table. The HWM remains the same.
A full table scan now will scan all those blocks upto HWM although we have 'emptied' out the table.
select count(*) from  t;
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0        277          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0        277          0           1

 as you see above, ‘delete from t’ doesn’t do anything to our  statistics( consistent gets, cpu )

-- Now, lets retry with Truncate 
SQL>drop table t;
Table dropped.
SQL>create table t as select * from all_objects where rownum <=10000;
Table created.
SQL>analyze table t compute statistics;
Table analyzed.
SQL>set timing on
SQL>Truncate table t;
Table Truncated.
Elapsed: 00:00:00.04
-- Note the Truncate took only .04 seconds ( compare to 11 seconds of delete )
-- Also, as seen below, you find that the Truncate DOESNT generate any undo entries for rollback.
SQL>select r.name "UNDO segment" ,t.status,t.cr_get,t.used_ublk,t.noundo
2 from v$session s,v$transaction t,v$rollname r
3 where t.addr=s.taddr AND
4 t.xidusn=r.usn AND
5 s.audsid=userenv('SESSIONID')
6 /
no rows selected
Elapsed: 00:00:00.01
-- V$transaction.NOUNDO would have been YES, since there is no UNDO and this cannot be rolled back.
-- Lets update the CBO statistics again
SQL>analyze table t compute statistics;
Table analyzed.
SQL>select table_name,blocks,empty_blocks from user_tables where table_name='T';
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 0 16
SQL>select blocks,extents from user_Segments where segment_name='T';
BLOCKS EXTENTS
---------- ----------
16 1

-- As you see above, used_blocks have now become 0 and there are 16 blocks above the HWM (ie empty_blocks).
Select count (*) from t
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0           1
Since the High water mark has been reset now, a Full table scan operation will complete in fraction of a second.
So is there a way to reset the HighWaterMark of the table without truncating the whole table ?  to improve Full table scan operations ? Sure there is .. ALTER TABLE <table_name>  MOVE;
will reset the HWM. for eg )
SQL> select blocks,empty_blocks from user_tables where table_name='T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
       146          110

SQL> alter table t MOVE;

Table altered.

SQL>  analyze table t compute statistics;

Table analyzed.

SQL> select blocks,empty_blocks from user_tables where table_name='T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
69	3
Now you see , the HWM has been reduced .
Also note that Truncate..REUSE STORAGE will not deallocate the extents as is the case with Truncate..DROP Storage, which is the default.
For eg)
SQL> drop table t;
Table dropped.
SQL> create table t as select * from all_objects where rownum <= 1000;
Table created.
SQL>  analyze table t compute statistics;
Table analyzed.
SQL> select blocks,empty_blocks from user_Tables where table_name='T';
    BLOCKS EMPTY_BLOCKS
---------- ------------
        19            5
SQL> select blocks,extents from user_Segments where segment_name='T';
    BLOCKS    EXTENTS
---------- ----------
24	3
-- as seen above, it has 5 Blocks above HWM and has 3 extents
SQL> truncate table t REUSE storage; 
Table truncated.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select blocks,empty_blocks from user_Tables where table_name='T';
    BLOCKS EMPTY_BLOCKS
---------- ------------
         0           24
SQL> select blocks,extents from user_Segments where segment_name='T';
    BLOCKS    EXTENTS
---------- ----------
24	3
As seen above, although the HighWater Mark has been reset to Zero,TRUNCATE ..REUSE storage didn’t deallocate those 3 extents. These can be used for further use by the same segment and this operation is faster than ‘drop storage’ that involves recursive sql to deallocate the extents (update fet$ in case of dictionary managed tablespaces).
-- Now lets deallocate those extents(ie reset to the intial extents)
SQL> truncate table t drop storage;
Table truncated.
SQL>  analyze table t compute statistics;
Table analyzed.
SQL>  select blocks,empty_blocks from user_Tables where table_name='T';
    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8
SQL>  select blocks,extents from user_Segments where segment_name='T';
    BLOCKS    EXTENTS
---------- ----------
         8          1
Also note that you wouldn’t be able to ‘truncate’ a table if it is referenced by enabled Foreign key constraint. You will have to disable the associated Foreign key constraint before truncating the table.
--------------------------------------------------------------------------------------------------------------------------------------------

-Thiru