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