Thursday 1 January 2009

Databases Oracle Difference between TRUNCATE and DELETE

Difference between TRUNCATE and DELETE

by
on in ,
This is one of most frequently asked question in Oracle interviews.
I tried to put all the differences i know,you are welcome to put your thoughts and comment on that

Delete

  1. It is a DML statement

  2. Can Rollback

  3. Can delete selective records

  4. It fires database triggers.

  5. It does not requires disabling of referential constraints

  6. Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deletedretains all of its original blocks.


Truncate



  1. It is a DDL statement
  2. Can’t Rollback
  3. Can’t delete selective records.It will delete all the records in table

  4. Doesn't fire database triggers

  5. It requires disabling of referential constraints

  6. Truncate moves the High Water Mark of the table back to zero.No row-level locks are taken,no redo or rollback is generated.
By
at
Labels: ,

2 comments:

  1. Anonymous11 January 2009 at 18:38

    -- To remove Control M characters
    vi filename
    Press escape key
    :%s/CTRL-V CTRL-M//g

    Umesh Joshi (Singapore)

    ReplyDelete
  2. Unknown21 October 2012 at 01:49

    Complete list of diff with examples

    http://arunkaushikoracle.blogspot.in/2012/10/diff-bw-delete-truncate-in-oracle-db-on.html

    Summary

    Delete
    1.DML data can be recovered by rollback before commit
    2.Remove any subset of data
    3.Delete is slower when table have numerous indexes and triggers
    4.DML triggers fire for delete mthod
    5.data can be recovered after commit by flashback method
    6.high water mark will remain the same
    7.data deleted info capture with returning clause
    8. you can not delete data if function based index is invalid
    9. can not delete data from complex views.
    10. Space is not freed

    Truncate
    1.Truncated Data can not be recoverd but truncated data is also logged
    2.truncate all or partition or subpartition data.
    3. truncate is faster as no logs we have to maintain ,no trigger firing
    4. reset the high water mark , space is released except reuse method
    5. can not truncate table indivisually if it is part of cluster
    6. can not truncate table with referential integerity constraint enable
    7. if table is not empty unuseable indexes become useable
    8. if domain index on table is invalid or in_progess state then we can not truncate the table
    8.we can not truncate a simple view , we can truncate MV but not simple view.

    For More Details on all this point with examples see my blog.

    ReplyDelete

Subscribe to: Post Comments (Atom)