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