Today, I am going to discuss about the facts behind the delete query and difference between delete query and truncate query.
Now you will come to know about why truncate query is faster than the delete query and what happens when you fire a delete query on a table.
Let me first brief you about delete query, Delete command is used to delete rows from the table you can also filter rows to delete from the table using where clause.
Syntax is shared below: Delete from users where name = ‘vishal’
Where users is table name and name is column of the table and using where clause I am filtering rows from users to delete from the table with name vishal.
And if query is fired without any where clause all the rows from the table will be deleted after delete command you need to fire rollback or commit to make the changes permanent or undo it.
Note: this command will cause all the delete triggers to fire on the table.
In case of truncate command all the rows from the table are removed and we cannot rollback the changes on the table because no DML triggers are fired on to the table.
Truncate is a DDL command whereas Delete is DML command and truncate is much faster than delete command.
When you fire delete command all the data gets copied to Rollback tablespace first then delete operation gets performed that is why when you fire rollback you get data back (the system get it from the rollback table space) . All this process takes time but when you fire truncate command it removes data directly from the table and you cannot retrieve data back from the rollback table space. That is why truncate is faster than delete command.