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

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;

Select all 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) */
SET SERVEROUTPUT ON;

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

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

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

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

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

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

      END IF;
   END LOOP;
END;

Select all 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';

Select all 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;

Select all Open in new window

6) and check the size using below query
 

select sum(bytes/1024/1024) from dba_extents where segment_name='TRUNCATEDELETE';

Select all 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;

Select all 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';

Select all Open in new window

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

Conclusion:

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.

Considerations:

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:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general003.htm

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

 $ORACLE_HOME/sqlplus/demo/demobld.sql

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).