Thursday 1 January 2009

Difference between TRUNCATE and DELETE

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.

1 comment:

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

    Umesh Joshi (Singapore)

    ReplyDelete