Difference Between TRUNCATE, DELETE, And DROP

The difference between TRUNCATE, DELETE, and DROP is one of most confused section.

Before doing any below actions please take backup of database using RMAN or exp/imp mothods.

TRUNCATE:

Truncate is used to delete all the records from the table.But it does not affect the table structure.We can insert the records in future as well.

For Example:

truncate table employees;

DROP (will have purge options):

Drop command is used to delete the entire table from the database including records.

Syntax:

drop table employees;

Above syntax will delete the entire table but deleted table stored in recycle_bin(we will restore it)

But purge options will drop the table permanentely.

drop table employees purge;

We can also delete views,index and so on.

DELETE:

Delete options will delete a specific records from the table or entire record from table.

Syntax:

delete from employees where columnname=data;  (delete specific record)

delete from employees;   (delete entire record from the table)

--------------

Delete - DML command to remove selective ( or all) records of table.

Truncate - Removes all rows, and commits automatically. Table structure will be preserved.

Drop - removes all rows, and remove the table structure as well.