Delete vs Truncate

Delete Command:

• It is DML command. 
• Command is for deleting the rows from the table.
• Can delete selective rows from the table using where clause.
• All rows can be deleted if no where clause is specified.
• It needs a commit or rollback command to complete the transaction or make it as permanent change.
• Delete will generate redo log entries.
• Delete command use the Undo tablespace to rollback the transaction.
• Delete operation is slow compared to truncate command as it generates redo log entries.
• Delete operation fires the delete triggers on that particular table.
• You can grant delete table privilege to a user.
• Delete never de-allocate the space. It needs a table reorganization to claim the free space.
• Delete doesn’t reset the high watermark.

Truncate Command:

• Truncate is DDL command. Obviously implicit commit is followed by truncate.
• Truncate command removes the entire rows from the table.
• Truncate command cannot be used for selective deletes.
• There is no rollback option with truncate command.
• There will not be any undo generation so this will not use undo tablespace.
• Truncate does not generate redo log entries.
• Truncate is faster compared to delete command.
• Underling triggers will not get fired for truncate command.
• You cannot grant truncate table privilege to a user.
• Truncate de-allocate the space.
• Truncate makes unusable index usable again.
• You cannot flashback truncate command.
• Truncate reset the high watermark.