delete vs truncate


  • delete is a DML statement
  • We can use where clause to selectively delete records
  • slower because it uses undo segment. (So that it can be rolledback)
  • triggers are fired in delete
  • won’t reclaim the space used by table
  • “On Delete cascade” from Oracle 12c


  • truncate is a DDL statement (Autocommit happens for DDL)
  • We cannot selectively delete records
  • It is faster
  • triggers are not fired in truncate
  • reclaim the space used by the table
  • “Truncate table <table_name> CASCADE” from Oracle 12c

Check space of a table

SELECT SUM(bytes), SUM(bytes)/1024/1024 MB
    FROM dba_extents
    WHERE owner = :owner
    AND segment_name = :table_name;

Updating space data dictionary

EXEC DBMS_STATS.gather_table_stats(USER,'STUDENT_INFO_TEMP0718',cascade => true);

Leave a Comment