What is the difference between DROP,DELETE and TRUNCATE.

What is difference between TRUNCATE & DELETE?

TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.

DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

TRUNCATE : You can't use WHERE clause

DELETE : You can use WHERE clause

Truncate: Drop all object's statistics and marks like High Water Mark free extents and leave the object really empty with the first extent.

Delete: You can keep object's statistics and all allocated space.

1>TRUNCATE is a DDL command whereas DELETE is a DML command.

2>TRUNCATE is much faster than DELETE.

Reason:When you type DELETE.all the data get copied into theRollback Tablespace first.then delete operation get performed.Thats why when you type ROLLBACK after deleting a table you can get back the data(The system get it for you from the RollbackTablespace).All this process take time.But when you type TRUNCATE it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.

4>In case of TRUNCATE Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.

5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.

truncate is ddl command.its faster than delete as it doesnt  have go through the rollbacks etc.truncate being a ddl is  auto commit.we can only truncate the whole table(cant use  where clause).once table is truncated we cant rollback the  changes.when a table is truncated the memory occupied is  released.that id the water mark is adjusted.  delete is a dml command and can be rolled back.is slower  than truncate as it is dml has to go through rollback  segments etc.we can use where clause with delete.when a  table is deleted memory occupied is not released ans also  the water mark is not adjusted. 

What is the difference between DROP,DELETE and TRUNCATE.

Drop and Truncate are DDL With Drop Command we can remove entire Table or columns from database. With Truncate we can remove the records in the table by keeping table structure.

Drop and Truncate are AutoCommit.

By using Delete command we can remove rows in the table but its not autocommit

DELETE
Delete is the command that only remove the data from the table. It is DML statement. Deleted data can be rollback. By using this we can delete whole data from the table(if use without where clause).If ew want to remove only selected data then we should specify condition in the where clause
SQL>delete from employee;(this command will remove all the data from table)
SQL>delete from employee where employee_name='JOHN';(This command will remove only that row from employee table where employee_name is JOHN');

DROP:
Drop command remove the table from data dictionary. This is the DDL statement. We can not recover the table before Oracle 10g. But Oracle 10g provide the command to recover it by using the command (FLASHBACK)

TRUNCATE:
This is the DML command. This command delete the data from table. But there is one difference from ordinary delete command. Truncate command drop the storage held by this table. Drop storage can be use by this table again or some other table. This is the faster command because it directly drop the storage