<

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

Published on
10,420 Points
4,220 Views
2 Endorsements
Last Modified:
Wasim Akram Shaik
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) */
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;

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.

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).
2
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month