EXPERT RESPONSE
Actually Tom has answered this question several times on his site. You can find the following answer at http://asktom.oracle.com/pls/ask/f?p=4950:8:267180628148108421::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1336002638218,
When an extent is allocated to a table (or index, etc) it belongs to that table until you either drop or truncate the table.
When you delete the data from the table -- the blocks will go onto the freelist for that table (assuming the amount of space on a block that was freed fell below the pctused). These blocks will be used for subsequent inserts and updates into this table.
When you delete data from the index -- if the block the index entry was on is now "empty" -- that block will go back onto the freelist to be used anywhere in the index struct. Else that block stays where it is and data that would naturally go onto that block (because of where it is in the b*tree) will go there.
Space is effectively reused when you delete. Your database will not show any new free space in dba_free_space -- it will have more blocks on freelists and more empty holes in index structures.
So....the answer is that once space is allocated it will never again show up as "free space" unless the storage is deallocated (using drop or truncate). That's why you don't see any difference.
For More Information
|
