[PL/SQL] Three delete methods Delete, Truncate, Drop

Posted Jun 29, 20201 min read

After reading this chapter you will learn the following:

  • What they have in common
  • Their differences


Same point:

They can delete data and clean up data that is irrelevant and irrelevant to the business.


1. Statement aspect

The delete statement is dml, and this operation will be put in the rollback segment, which will take effect after the transaction is submitted; if there is a corresponding trigger, *it will be triggered when it is executed.

  • truncate, drop is ddl, the operation takes effect immediately, the original data is not placed in the rollback segment and cannot be rolled back. The operation does not trigger the trigger.
2. Delete content
  • truncate and delete only delete the data without deleting the table structure(definition)
  • The drop statement will delete the constraints, triggers, and indexes that the structure of the table is dependent on;
  • delete/drop can delete views, indexes, triggers, etc. truncate is only for tables.
3. High water level HWM
  • The delete statement does not affect the area occupied by the table(Extent), and the high watermark remains in its original position.
  • The drop statement releases all the space occupied by the table.
  • The truncate statement releases space to minextents extents by default, unless reuse storage is used; truncate will reset the high watermark(back to the beginning).
4. Speed
  • Generally:drop> truncate> delete