Printable View
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
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
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.,
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.
If the table is truncated and then populated ones again,
should the indexes be recreated ?
Badrinath
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.
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.
As usuall, your conceptual Q`s clears perfectly with Jmodic explaination. Great Jmodic !!
it's like you erase text from a piece of paper with rubber (delete) or simply cut the paper (truncate) with a sciessor :o
Kudos to Jurij.
One more point:
DELETE is a DML where TRUNCATE is DDL.
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. **/
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]