Difference between Delete and Truncate Oracle sql plsql interview question and answer.

Difference between Delete and Truncate Oracle sql plsql interview question and answer. Delete and Truncate is used to remove the data from the relational table. SQL developer have to decide where to use these both concepts. By understanding the concept, it is useful for handling the data. Well analysis of all the rows to be deleted before using truncate command.
DELETE:
It is a Data Manipulation Language command.
Syntax:
Delete from tablename where condition;
Delete from tablename;

Delete command is slower than truncate.

Delete contain the where clause.

It is used to remove or delete the specified row or tuple from the table using where clause. In absence of where clause, it is used to delete all the rows or tuples from the table.

Deleted Data can be backed using the rolled command.

Trigger is fired.

It doesn’t remove the records permanently.

In order to use delete command, we need to delete permission over the table.

Integrity constraint will be same.

It maintains the transaction log of the removed rows.

It uses the row lock.

It uses the undo space more than truncate.

Autoincrement counter is not reset.

It can be used with indexed views.

It uses the more transaction space.

It does not recover space.

Delete never lowers the high-water mark.

Flashwork  work across .

It is less used for deleting the large tables.

If foreign key is enabled, data can be deleted.

It does not reset the high-water mark.

It is not auto-commit statement.

It does not remove the table size space.

It keeps tracking for the deleted rows.

Manual commit

It deletes the data without resetting the table identity.

From oracle 12c,on delete cascade which deletes the parent and child table.

Data is copied to undo tablespace before delete operation.

Foreign key is present.

Transaction performance is not better than truncate.

HVM is not updated and it hold the undo tablespace.

Table’s row which is deleted which are the logged operation and number of rows which are deleted present in the transaction
log makes it slower.

Truncate:
It is a Data Definition Language command.

Syntax:
Truncate table tablename;

Truncate command is faster.

Truncate don’t contain the where clause.

It is used to delete all the rows or tuples from the table.

Deleted Data cannot be backed using the rolled command.

Trigger is not fired.

It removes the records permanently.

In order to use truncate command, we need to
Alter permission over the table.

Integrity constraint will not be removed.

It don’t maintain the transaction log of the removed rows.

It uses the table lock.

It does not use the undo space like delete.

Autoincrement counter is reset.

It cannot be used with an indexed view.

It uses the less transaction space.

It recovers space.

Truncate lowers the high-water mark.

It prevents flashwork.

It is used for deleting the large tables.

If foreign key is enabled, data cannot be deleted. We have to disable the foreign key for the data to be deleted.

It reset the high-water mark.

It is an auto-commit statement.

It removes the table size space.

It doesn’t keep tracking for the deleted rows.

Automatic commit

It reset the particular table identity.

From oracle 12c, truncate table table_name cascade which deletes the parent and child table.

Data is not copied to undo tablespace before truncate operation.

Foreign key is not present.

It’s transaction performance is better than delete.

HVM is updated and it don’t hold the undo tablespace.

Table’s row which is deleted which are not the logged operation and number of rows which are deleted not present in the transaction log. Deallocation of data pages of the table which really helps in faster.