Difference between DELETE, TRUNCATE and DROP

DELETE Statement: 

• DELETE is a DML command.
• Can rollback in DELETE.
• Triggers get fired.
• Can use conditions (WHERE clause) in DELETE.
• DELETE does not reset the High Water Mark for the table

TRUNCATE statement: 

• TRUNCATE is a DDL command.
• TRUNCATE is much faster than DELETE.

Reason: When you type DELETE all the data get copied into the Rollback Tablespace first. Then delete operation gets performed. That way when you type ROLLBACK after deleting a table, you can get back the data (The system get it for you from the Rollback Tablespace). All this process takes time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback Tablespace. That's why TRUNCATE is faster. Once you truncate you can’t get back the data.

• Cannot roll back in TRUNCATE. TRUNCATE removes the record permanently.
• The trigger doesn't get fired
• Cannot use conditions (WHERE clause) in TRUNCATE.
• TRUNCATE command resets the High Water Mark for the table
• This command is used to delete all the rows from the table and free the space containing the table.
• When a table is truncated all the references to the table will be valid.

DROP Statement: 

• DROP is a DDL command.
• No DML triggers will be fired.

The DROP command is used to remove an object from the data dictionary. If you drop a table, all the rows in the table are deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back, so be careful while using the DROP command. When a table is dropped all the references to the table will not be valid.

If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, and grant or access privileges on the table will also be dropped, if want uses the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. 

We cannot recover the table before Oracle 10g. But Oracle 10g provides the command to recover it by using the command (FLASHBACK)