Difference between Delete and Truncate in oracle sql plsql interview question and answer for fresher,intermediate and experienced.

 "Most important question asked in oracle sql plsql interview"

Introduction:

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.

Difference:

Delete	Truncate
It is a Data Manipulation Language command.	It is a Data Definition Language command.
Syntax: Delete from tablename where condition; Delete from tablename;	Syntax: Truncate table tablename;
Delete command is slower than truncate.	Truncate command is faster.
Delete contain the where clause.	Truncate don’t 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.	It is used to delete all the rows or tuples from the table.
Deleted Data can be backed using the rolled command.	Deleted Data cannot be backed using the rolled command.
Trigger is fired.	Trigger is not fired.
It doesn’t remove the records permanently.	It removes the records permanently.
In order to use delete command, we need to delete permission over the table.	In order to use truncate command, we need to Alter permission over the table.
Integrity constraint will be same.	Integrity constraint will not be removed.
It maintains the transaction log of the removed rows.	It don’t maintain the transaction log of the removed rows.
It uses the row lock.	It uses the table lock.
It uses the undo space more than truncate.	It does not use the undo space like delete.
Autoincrement counter is not reset.	Autoincrement counter is reset.
It can be used with indexed views.	It cannot be used with an indexed view.
It uses the more transaction space.	It uses the less transaction space.
It does not recover space.	It recovers space.
Delete never lowers the high-water mark.	Truncate lowers the high-water mark.
Flashwork  work across .	It prevents flashwork.
It is less used for deleting the large tables.	It is used for deleting the large tables.
If foreign key is enabled, data can be deleted.	If foreign key is enabled, data cannot be deleted. We have to disable the foreign key for the data to be deleted.
It does not reset the high-water mark.	It reset the high-water mark.
It is not auto-commit statement.	It is an auto-commit statement.
It does not remove the table size space.	It removes the table size space.
It keeps tracking for the deleted rows.	It doesn’t keep tracking for the deleted rows.
Manual commit	Automatic commit
It deletes the data without resetting the table identity.	It reset the particular table identity.
From oracle 12c,on delete cascade which deletes the parent and child table.	From oracle 12c, truncate table table_name cascade which deletes the parent and child table.
Data is copied to undo tablespace before delete operation.	Data is not copied to undo tablespace before truncate operation.
Foreign key is present.	Foreign key is not present.
Transaction performance is not better than truncate.	It’s transaction performance is better than delete.
HVM is not updated and it hold the undo tablespace.	HVM is updated and it don’t 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.	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.