HomeОбразованиеRelated VideosMore From: Ram Gupta

Difference Between Delete and Truncate

94 ratings | 14341 views
Below is link for Useful Pl/SQL Books http://goo.gl/XMy0tt ---------------------------------------------------------- Difference Between Delete and Truncate in Detail On bigger picture they serve the same purpose but there are many Differences listed with examples Point Delete Truncate 1. Data Recovery Delete: Come under the DML Category, we need to commit or Rollback explicitly to make the changes permanent, so we can recover the data by Rollback command fully with in a session or up to a point if Save Points are used Fall In DDL Category (DDL Command issue the Auto commit implicitly) so no chances of Recovery even not using the Flashback table method. But Truncate operations are also logged , they didn’t generate redo SQL but they are logged , view for truncated data info V$LOGMNR_CONTENTS 2. Data Removal Delete Can remove all or selected data using the Where Clause predicates. Or we can say delete any subset of rows We can Truncate complete table or a partition or sub partition of a table. 3. Speed Delete is Slower because oracle maintain the redo logs for Read Consistency (so that every session connected can see a consistent data at a given point of time ) Delete is very time consuming activity especially when table have numerous indexes and Triggers associated with table Faster as no data logs are maintained no associated trigger firing. 4. DML Triggers Firing DML (Delete) triggers associated with table will fire. DML Trigger will not fire in case of truncate method. 5. Flashback Technology Data can be recovered even after commit operation using Flashback Table options Flashback_transaction_query table will give what to recover and up to which point. Data cannot be recovered in truncate method by Flashback table option. 6. Referential Integrity Constraint Behavior if we don’t have related data in child table then we can delete the data from the parent table or we have variants like On Delete Cascade & on Delete set Null. We can’t truncate a table with enable Referential Integrity Constraint, even there is no data in the child table, we have to disable or drop the constraint if we want to truncate the table. Exception: Truncate is possible if the FK is self-referential means primary key and foreign key are on the same table. 7. Space De allocation or Space Utilization No extent reset with delete when deleting rows from a table, extents are not de allocated, So if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. Truncate: When a table is truncated it will free the space allocated except in case of reuse storage clause. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations .All extents are de allocated leaving only the extents specified when the table was originally created .Example So if the table was originally created with min extents 3, there will be 3 extents remaining when the tables is truncated. When you truncate a table, NEXT is automatically reset to the last extent deleted. 8. High Water Mark Delete will not reset the high water mark Truncate will reset the High Water mark which is very important for performance point of view as in case of full table scan and full index scan oracle will read all the block under high water mark this makes a lot of difference in terms of performance. 9. Cluster No as such restriction with delete. You cannot individually truncate a table that is part of a cluster. You must truncate the cluster, Delete all rows from the table, or drop and re-create the table. 10. Information Capturing Delete : we can capture the row information what we have deleted using Delete Method, f you are deleting multiple records then use composite data types (collections & records) Truncate Don’t have this feature of capturing the deleted records. 11. Function Based Index Impact DELETE You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index. Truncate: No as such restriction 12. UNUSABLE Indexes Delete no as such feature. Truncate if table is not empty then truncate make all unusable indexes to useable. 13. Complex views You cannot delete data from a Complex view except through INSTEAD OF triggers. But we can delete data from simple Views and MV. We cannot truncate a view simple or complex but you can truncate MV with special Features like Preserve MV Logs and Purge MV Logs. 14. Privileges Delete You need to provide delete table privilege on object. Truncate you must have drop table privilege there is no truncate table privilege exists. 15. Domain Index No as such restriction You cannot truncate the object having domain index in invalid or In progress state
Html code for embedding videos on your blog
Text Comments (13)
jagadish m (1 year ago)
Saurabh Singh (1 year ago)
one small correction, with truncate table clause is used not from as it's a ddl command.
andy m (2 years ago)
Hey nice explanation .... :)
Sappho Dawson (2 years ago)
TRUNCATE can be rollback :)
abhimaniu kapil (2 years ago)
We can have triggers on truncate as well? You mentioned triggers will not fire on truncate.
reggaemylitis1968 (2 years ago)
Thanks very much. You make it so simple. Appreciated.
Ram Gupta (2 years ago)
Thank you so much for you feedback +Reggaemylitis , Please stay tune with the channel by Subscribe. Keep giving feedback :) For subscribe https://www.youtube.com/c/ramguptaoracle?sub_confirmation=1
SANTOSH KUNDAHRE (2 years ago)
Very nice and Good Explanation, Please clear me my Daught.... I Am using SQL Server 2014 Management Studio If we use following Sequence of statement then what will be the result Begin transaction Delete from Table_Name; Rollback; In Delete and Truncate, Deleted and Truncate data can be rollback.
Ram Gupta (2 years ago)
Thank you so much for your feedback Santosh+ , and sorry to say as I don't have experience on SQL Server database on any version. I am not into position reply answer of this question.
siddhartha rao (3 years ago)
Good Video and Nice Explanation..
Ram Gupta (3 years ago)
Thanks For feedback Siddhartha
lmca2010 (3 years ago)
good videos :) ps consider referring that if a table is "pointed" by a child enabled FK , TRUNCATE is not allowed (even if there're no child records there) .
Ram Gupta (3 years ago)
+lmca2010 Thanks for your feedback Imca :)

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.