Results 1 to 19 of 19

Thread: difference between delete and truncate

  1. #1
    Expert Member
    Join Date
    Apr 2006
    Answers
    124

    difference between delete and truncate

    Hi

    can any one tell me the difference between delete and truncate statement in mysql . which is better to use?


  2. #2
    Junior Member
    Join Date
    Mar 2007
    Answers
    1

    Re: difference between delete and truncate

    Hi,
    This is Veera Reddy
    The difference between delete and truncate is.
    if u use delete, it will delete row by row and after deletion there is a possibility of rollback your data where as if you use truncate it will delete the entire table i.e., with structure and after deletion it will recreate the structure and there is no possibility of rollback of data once you apply truncate.


  3. #3
    Expert Member
    Join Date
    Jun 2006
    Answers
    410

    Re: difference between delete and truncate

    Just wanted to add one more point.

    Truncate is a DDL command and Delete is a Dml command.

    DDL statements perform commit implicitly. That is why your changed are committed automatically when ever you perform a DDL statement ( Create,Alter etc).

    DML statements does not commit your changes. You have to commit explicitly inorder to save your changes. Examples of DML commands are insert,update, delete etc.


  4. #4
    Expert Member
    Join Date
    Nov 2006
    Answers
    518

    Re: difference between delete and truncate

    Good one from veera_KK and jamesravid


  5. #5
    Junior Member
    Join Date
    Jun 2007
    Answers
    28

    Re: difference between delete and truncate

    Add one more point also

    When Delete the Particular row the Corresponding Delete Trigger(if exists) Fire.
    In Case of Truncate the Trigger is not fired.


  6. #6
    I am the DON
    Join Date
    Apr 2006
    Answers
    99

    Lightbulb Re: difference between delete and truncate

    Simply to say -

    DML statements (Delete) can be rollbacked where DDL (Truncate) are autocommit.

    Sanghala

    If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.

  7. #7
    Contributing Member
    Join Date
    May 2007
    Answers
    60

    Re: difference between delete and truncate

    'truncate' requires no condition bcoz it deletes the total table once where as 'delete' requires condition bcoz it deletes the data row by row.....


  8. #8
    Junior Member
    Join Date
    Jul 2007
    Answers
    2

    Arrow Re: difference between delete and truncate

    Truncate is a DDL command and Delete is a DML command. Hence Truncate operation can not be rolled back, where as delete operation can be rolled back. where clause can be used with delete command and not with the truncate command.


  9. #9

    Thumbs up Re: difference between delete and truncate

    Both delete and truncate will delete the rows in a table. But there are some differences between them

    1. Delete will delete rows on the table based on the condition, either may be a single row or many rows
    eg., Delect * from tablename where condition;
    Truncate will delete all the rows in the table.

    2. Truncate is a DDL command but Delete is a DML command.

    3. Rollback is possible after the delete command, but since truncate is a ddl command rollback is not possible.


  10. #10
    Junior Member
    Join Date
    Oct 2007
    Answers
    6

    Re: difference between delete and truncate

    hi
    i want to add one more point
    truncate is faster then the delete and doesn't use as much undo space as delete


  11. #11

    Re: difference between delete and truncate

    if u delete a record from a table the deleted record will be stored in log file.u can retrieve it later.But its not in the case of truncate.it will delete frm the log file also


  12. #12
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: difference between delete and truncate

    Since Delete is DML it deletes only the data.
    Truncate is DDL it changes the table structure it self, which is not allowed while another user is connected to the table.


  13. #13
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: difference between delete and truncate

    Delete
    At the simplest level, delete scans the table and removes any rows that match the given criteria in the (optional) where clause. It generates rollback information so that the deletions can be undone should it be necessary. Index entries for the deleted rows are removed from the indexes. You must commit to make the deletions permanent.

    When deleting rows from a table, extents are not deallocated, so if there were 50 extents in the table before the deletion, there will still be 50 after the deletion. In addition the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may (still) take a long time to complete - because a full table scan always scans up to the HWM. So, by example, if a select count(*) from very_large_table; took 15 minutes to complete before all the rows were deleted, you will find that it still takes about 15 mins after the deletion - because Oracle is still scanning every single block up to the HWM - even though some (or all) of the blocks may have no data in them.

    Truncate
    Truncate, on the other hand, simply moves the high water mark on the table right down to the beginning. It does this very quickly, and does not need to be committed. Once you truncate a table, there is no going back. Indexes are also truncated. There is no facility to be able to specify which rows to 'delete' as you can with the where clause on the delete command.

    When a table is truncated, all its extents are deallocated leaving only the extents specified when the table was originally created. So if the table was originally created with minextents 3, there will be 3 extents remaining when the tables is truncated.

    If you specify the reuse storage clause, then the extents are not deallocated. This saves time in the recursive SQL department if you intend to reload the table with data from an export for example, and can reduce the time it takes to do the import as there is no need to dynamically allocate any new extents.


  14. #14
    Junior Member
    Join Date
    Aug 2006
    Answers
    3

    Re: difference between delete and truncate

    Quote Originally Posted by bvani View Post
    Hi

    can any one tell me the difference between delete and truncate statement in mysql . which is better to use?
    with delete operation one can rollback the data; this shows after performing delete operation , data is not completely deleted;

    but in case of trancate , one cannot retrive the data using rollback operation....................


  15. #15
    Junior Member
    Join Date
    Aug 2006
    Answers
    3

    Re: difference between delete and truncate

    Quote Originally Posted by bvani View Post
    Hi

    can any one tell me the difference between delete and truncate statement in mysql . which is better to use?
    trancate delets the data permanently; ie one cannot perform rollback operation on the trancated data.........

    in case of delete one can retrive the data the deleted data using rollback operation.......................


  16. #16
    Junior Member
    Join Date
    Dec 2007
    Answers
    2

    Re: difference between delete and truncate

    truncate does not take any data to the rollback segment or undo tablespace.
    truncate is fast.
    truncate delete's the data permanently.

    delete will copy the data to the undotablespace for rollback.
    delete is slow.


  17. #17
    Junior Member
    Join Date
    Feb 2007
    Answers
    1

    Re: difference between delete and truncate

    hi,
    delete removes data in the table

    but truncate deletes the structure of the table.


  18. #18
    Contributing Member
    Join Date
    Sep 2007
    Answers
    35

    Re: difference between delete and truncate

    delete means it will delete all rows.and space of deleted rows will not be released.
    but when truncate the table all the rows will be deleted & space of rows also deleted.
    when deleting we can roll back but in trucate roll back is not possible.


  19. #19
    Junior Member
    Join Date
    Dec 2007
    Answers
    18

    Re: difference between delete and truncate

    The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

    TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesn't use as much undo space as a DELETE.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact