(Oracle) Truncate or Delete which one to use...??

Published on
11,927 Points
2 Endorsements
Last Modified:
Truncate is a DDL Command where as Delete is a DML Command.

Both will delete data from table, but what is the difference between these below statements

truncate table <table_name> ??
delete from <table_name> ??

The first command cannot be rolled back, the second command can be (ie rollback can work with delete). But in terms of performance which one is better? Well it depends on the scenario where we use the statements...

For all the temporary tables (intermediate tables) to have a better performance we should always use truncate command. Because, apart from deleting the whole data from the table, truncate command also releases the space(extents) used by the table and reset the high and low water marks of the table (high water mark is just a note to the oracle writer so as to write from the next extent). When you use delete command, high water mark doesnot get reset, hence the storage space doesnt get released.

To test the scenario better, we do need to perform an exercise (for this you need atleast 50 mb of storage).

1) create a test table like this
create table truncatedelete
as select * from emp;

Open in new window

2) check the number of rows in emp table(recommended rows are to be less than 50)

3) now to just to populate the data in our temp(truncatedelete table) use the below script
/* Formatted on 2011/12/17 00:36 (Formatter Plus v4.8.8) */

   v_count   NUMBER := 1;
   INSERT INTO truncatedelete
      SELECT *
        FROM scott.emp;

   FOR i IN 1 .. 31
      INSERT INTO truncatedelete
         SELECT *
           FROM truncatedelete;

      DBMS_OUTPUT.put_line ('v_count' || v_count);
      v_count := v_count + 1;

      IF MOD (v_count, 10) = 0
         DELETE FROM truncatedelete
               WHERE ROWNUM < 1000;

         DBMS_OUTPUT.put_line ('Deleted 9 rows');
      END IF;

      IF v_count > 15
         DELETE FROM truncatedelete
               WHERE ROWNUM < 10000;
                  INSERT INTO truncatedelete
         SELECT *
           FROM truncatedelete;

      END IF;

Open in new window

4) now that the data has  been populated in our table check out the size of the table using the below query
select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';

Open in new window

  You need to have access to the dba_views (use system schema for this purpose)
   In my db the size is shown as 40 mb

5) after this execute the below query
delete from truncatedelete;

Open in new window

6) and check the size using below query
select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';

Open in new window

7) you will not see any difference in size , even though there is no data in the table - hence you can conclude that delete does not release the storage space..

8) and now execute the below query
truncate table truncatedelete;

Open in new window

   now check the size again using the above query from dba_extents
select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';

Open in new window

   you will see that table unused space has shrunk and that space has been released.


So in the case of temporary/interface tables where data needs are only for a temporary basis, and you need to keep the table, it would be better to use truncate rather than delete. Truncate also saves the effort of having to drop and recreate - but that is a different story.


Few things to be considered while using both the statements are, while using delete and truncate if there are any referential constraints, the operation gets aborted because of the child/foreign records.

Also in case of delete, as the delete is a dml command, until and unless a commit happens all the transactional information gets recorded in database buffer caches and redo log space ie., only when a commit happens the information gets written in archive logs. Hence Truncate is faster than Delete. As Truncate statement does not need a commit as it is a DDL Command.

Also incase of delete, if there are any triggers enabled in the table(row level or statement level) a delete statement will fire the trigger . Where in a truncate statement the trigger doesn't get fired.

Further information about these statements can be read from the below sites:



Incase some one is unable to find the employee table in their database schemas. you can get it created from the scripts stored in the server at the following location


Depending upon the scenario and requirement, the different statements can make a big difference and should always remove data with caution - always check twice before you do - especially with the truncate statement (remember - no rollback - have to go to backup).
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free