what is the difference truncate or delete - All Records

very basic question: to delete data for more than 100 tables and wanted to retain the table structure and index.

Which one is the best option to remove all data from table TRUNCATE or DELETE?

truncate is DDL.

which means

- implicit commit before the statement

- implicit commit after the statement

- the high water mark of the table will be reset

It will use no redo and can not be rolled back.

delete is dml

it means

if no commit after the statement

The HWM will not be reset.

commit/rolled back is possible

DROP : 

It is a DDL. It is used to drop the whole table. With the help of the “DROP” command we can drop the whole structure in one go it removes the object from the schema. 

DROP table table-name purge;

can’t restore the table by using the “ROLLBACK” command because it auto commits.

DELETE : 

it is dml It is used to delete one or more tuples of a table. With the help of the “DELETE” command, we can either delete all the rows in one go or can delete rows one by one. i.e., we can use it as per the requirement or the condition using the Where clause. It is comparatively slower than the TRUNCATE command. The TRUNCATE command does not remove the structure of the table.

    DELETE from table-name where condition;

TRUNCATE : 

It is DDL. It is used to delete all the rows of a table. With the help of the “TRUNCATE” command, we can’t delete the single row as here WHERE clause is not used. By using this command the existence of all the rows will be deleted. It is comparatively faster than the delete command. 

TRUNCATE table table_name;