What is the difference between “Truncating a table”, “Deleting all the records from the table” and “Dropping the table”?
1. Delete command is used to remove records from a table. For deleting a particular set of records based on a condition, WHERE clause can be used. Without a WHERE clause it deletes all the records from the table.
2. DELETE is not an AUTOCOMMIT transaction. So, you need to COMMIT or ROLLBACK the operation to keep or discard the changes.
3. Moreover, DELETE operation can fire the triggers defined on the table (if any).
1. It is used to delete all the rows from the table but retain the structure.
2. This operation CANNOT BE ROLLED BACK.
3. No triggers can be associated with a truncate (not everyone is aware of this fact).
Important: Truncate is faster than Delete. Because: It does not have a rollback option, so it need not use an undo space and use resources for making provision for a rollback operation.(Frequently asked interview question.)
1. It is used to remove a table from the database. Unlike Truncate which deletes the records and retains the table structure, this command removes the table structure itself from the database.
2. All the indexes and privileges associated with the table are also removed.
3. This operation also doesn’t support rollback.
4. No triggers can be associated to a drop command.
Important: A major difference is that DROP and TRUNCATE are DDL commands, whereas DELETE is a DML command. (This is why DELETE operations can be rolled back (undone), while DROP and TRUNCATE operations cannot be rolled back.)
Note: There’s a tricky way to rollback the drop of a table as shown below.(Supported Oracle 10g onwards)