Saturday, April 18, 2009

Oracle Storage and High water Mark

Lets understand how data is stored in Oracle ...

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.

No comments:

Post a Comment