Delete Vs Truncate - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 12 of 12

Thread: Delete Vs Truncate

  1. #11
    Join Date
    Jan 2001
    Posts
    642
    Thank you all,That was an excellent explaination, I have got it clearly now.
    In this section, jmodic say that TRUNCATE is so simple that actually data remains in the blocks while only the HWM will be reset to the first block of the first extents.
    Before truncating, IF i know the HWM value , is it possible to , Kind of "ROLLBACK" by updating the HWM value back in the dict tables(Say as a SYS user).

    Badrinath

    /*
    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. **/


  2. #12
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    No you cannot, there are two obvious reasons

    1.- You are recommended not to touch objects owned by SYS if you dont want to corrupt your database
    2.- This one is pretty obvious, when you truncate a table the space is freed so if some other segment requires space and Oracle find the required space in that space where you just freed with truncate command then Oracle will allocate that to the segment that requested free space. Now in an busy system there is NO way you can assure that the space you freed will not be requested by any other segments and therefore overwritten on the data on those data blocks. If the mechanism you are describing works then everything in the database would be in a total chaos.
    Let's say ten tables are truncated at the same time and 20 segments are requesting freespace if Oracle offers the mechanism you mentioned (undo the truncate command) then there is NO point at all freeing the space (if the free space is still kept for this *undo* mechanism then what is the point of truncating where other than being faster when we truncate we really want to free spaces) The only similar mechanism i would think about implementing is something like windows recycle bin, when you delete files they are still kept for undo your operation but when you empty them they are eligible to be overwritten by other operations/data.

    [Edited by pando on 03-16-2001 at 04:30 PM]

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