delete vs truncate

delete

• 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

• 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);