Each table in oracle is made up of a number of extents and each extent is made up of a number of blocks.
ie Table = x number of extents
extent = y number of blocks
segment = set of extents
A word about high water mark
HWM: Its divides segments into used and unused
Blocks below HWM contain data and those beyond HWM are free.
DATA
_____________________________HWM__________________________
FREE BLOCKS
Resetting HWM:
Lets say that you fill your table with 4 million records. As a result, you fill in several extents. Now lets delete half of these records. Now your table still has those extents allocated but lots of them are empty. When we run a query on this table with a WHERE clause, all the extents are scanned including the empty ones which is obviously a performance overhead.
So to reset your HWM, use this statement ALTER TABLE MYTABLE MOVE TABLESPACE USERS.
Note: When data is inserted , HWM is automatically adusted.
When data is deleted, HWM is not adjusted automatically.
DELETE * v/s TRUNCATE
TRUNCATE resets the HWM, DELETE doesn't.
