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).
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;
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..
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:
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).
Comments (0)