Difference between Delete and Truncate

ifference between TRUNCATE, DELETE and DROP commands

Submitted by admin on Sat, 2006-02-11 02:07 

DELETE

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        10

TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.

SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
         0

DROP

The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

SQL> DROP TABLE emp;

Table dropped.

SQL> SELECT * FROM emp;
SELECT * FROM emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

From Oracle 10g a table can be "undropped". Example:

SQL> FLASHBACK TABLE emp TO BEFORE DROP;

Flashback complete.

PS: DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. As such, DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.

‹ What are the difference between DDL, DML and DCL commands?upHow does one escape special characters when writing SQL queries? ›

» 

• Login to post comments

With dropping a table, also bear in mind that you lose any associated grants / constraints etc. and if you want to recreate the table, you would need to recreate these - and it may be that the user dropping the table may not have the rights to re-grant etc. - so dropping is always an extreme measure! If you're constantly dropping / recreating you should probably be using temporary tables for one-off jobs or global temporary tables for frequently running processes..

TRUNCATE V/s DELETE

Truncate	Delete
TRUNCATE is a DDL command	DELETE is a DML command
TRUNCATE TABLE always locks the table and page but not each row	DELETE statement is executed using a row lock,                                             each row in the table is locked for deletion
Cannot use Where Condition	We can specify filters in where clause
It Removes all the data	It deletes specified data if where condition exists.
TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions.	Delete activates a trigger because the operation                                are logged individually.
Faster in performance wise, because it is minimally logged in transaction log.	Slower than truncate because, it maintain logs for every record
Drop all object’s statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table	keeps object’s statistics and all allocated space. After a                       DELETE statement is executed,the table can still contain empty pages.
TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction lo	The DELETE statement removes rows one at a time                       and records an entry in the transaction log for each deleted row
If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column	DELETE retain the identity
Restrictions on using Truncate Statement 1. Are referenced by a FOREIGN KEY constraint. 2. Participate in an indexed view. 3. Are published by using transactional replication or merge replication.