Delete Vs Truncate
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Delete Vs Truncate

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,


    To my knowledge, when delete statement is issued the data is actually deleted from the blocks and the space in the blocks will be realeased. But this space can be utilized when the pctused level has been reached.
    Also, Delete operations are slow(As it acts on blocks directly)

    Could any one tell me how the Truncate command actually work.. Because, truncating at table as big as 25Million records take very few milliseconds while actual delete takes hours.

    Badrinath

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    The delete operation would delete the records and the free space would not be released for the other schema objects to use. (i.e. only the table whose records had been deleted would be able to use the freespace, for its futrure inserts) The operation of Truncate would preserce the schema, but would drop the recrds and release the freespace. This inturn would allow the other database objects to use this free space. It also would lower the high water mark accordingly.

    Hope this would help you.
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Have a look at the documentation of Oracle Utilities. It gives the clear picture of all the options INSERT/TRUNCATE/REPLACE and options benefits and performance etc.,
    Reddy,Sam

  4. #4
    Join Date
    Mar 2001
    Posts
    45
    Hope the pretty fastness for TRUNCATE command is for one more reason.

    Delete also takes care of the undo action. So data needs to be push off in the RBS. (You can either rollback or commit)

    Where as Tuncate will not give you that option for (rollback or commit) it will not touch the Rollback segment at all.


    Thanx
    Ramesh.
    ______________________________
    There is nothing Impossible.
    Even Impossible says
    I M POSSIBLE

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    If the table is truncated and then populated ones again,
    should the indexes be recreated ?

    Badrinath

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Nope ! You have to recreate, if you did drop indexes before TRUNCATE. As you know if you don't drop your load takes lot time, usually better loading data after dropping indexes and recreate 'em if you are dealing with millions of rows.

    If you don't drop, the indexes stay, but takes lot time to load.
    Reddy,Sam

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why TRUNCATE is so fast as opposed to DELETE?

    When you do delete, system has to scan the whole table and for each record it deletes it has to create an redo as well as undo entry (redo logs and rollbacks). On top of this, it has to maintain/change each block header, maintain all the indexes defined on the table, possibly fire some triggers etc etc. On top of this it has to maintain freelists, each block must be read into buffer pool, etc etc. All this operations are preatty expencive, most of them is heavily I/O bound (the slowest operations on the system). As a result, the rows are actually deleted (erased) from the blocks.

    On the other hand, when using TRUNCATE system has to perform one thing only: reset the high watter mark (HWM) to the first block of the first extent. This is a simple and extremely fast operation (if compared to all the operations described above), performed only on a couple of dictionary tables. Nothing more. So as a result, the rows actually remain written in the blocks, but anything above the HWM is "nonexistant"/"unreadable" for the system, so the database can't read those rows any more.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    As usuall, your conceptual Q`s clears perfectly with Jmodic explaination. Great Jmodic !!
    Reddy,Sam

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    it's like you erase text from a piece of paper with rubber (delete) or simply cut the paper (truncate) with a sciessor :o

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Kudos to Jurij.
    One more point:
    DELETE is a DML where TRUNCATE is DDL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width