Difference between DELETE and TRUNCATE in Oracle

I have summarized main difference between Truncate and Delete command in Oracle.

Truncate Delete
DDL command DML command
Cannot be rolled back Can be rolled back
Associated with commit Need to commit explicitly after
Will not fire trigger associated with the table Will fire trigger associated with the table
Cannot be used with where clause Where clause can be used
Reset the high water mark No association with high water mark
Generally executes faster than delete Generally executes slower than truncate
Cannot run truncate for a parent table with constraints Delete can be applied after deleting the child records

Note that TRUNCATE table has two variants

1. Truncate table table_name;

or

Truncate table table_name drop storage;

(drop storage is by default)

Here Truncate reset the high water mark

2.  Truncate table table_name REUSE storage;

In this case Oracle will not reset the high water mark and same storage space will allocated for new entries in future.

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

4 thoughts on “Difference between DELETE and TRUNCATE in Oracle”

  1. 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

  2. I’ve worked a lot with Oracle and have started working more with Teradata recently. Interestingly enough, Teradata has no TRUNCATE command. There is a DELETE ALL command vs. just the regular delete but I am not 100% sure if DELETE ALL is exactly equivalent to TRUNCATE in Oracle.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s