High Level Watermark(HLW) in datablock:
- High Level Watermark is a term used with table segment in DB to show the amount of datablock occupied(level) by the table data.
- As per PCTUSED and PCTFREE mentioned for datablock, the data will fillup in datablocks.
- When a table created(without data), the High Level Watermark will be on left most datablock(consider the datablocks arrange horizontally).
- After inserting few records, it fills few datablock, and High Level Watermark will increase.
- After deleting few intermediate records, it will vacate few datablock, but still the High Level Watermark will be same. -> Till REBUILD/TRUNCATE/ SHRINK.
- Once we will TRUNCATE the table the High Level Watermark will be reset to initial position (leftmost datablock).
–> MSSM : Manual Segment Space Management.
-> can define PCTFREE and PCTUSED
–> ASSM : Automatic Segment Space Management.
-> can only define PCTFREE
PCTUSED and PCTFREE:
Let’s understand with an example:
Suppose PCTUSED = 50 : It is used to find out how much percentage of the block will be used to store rows.
PCTFREE = 20 : It is used to find out how much percentage of the block will be used to store rows resulting from further updates to the rows in the same datablock.
–> After deleting a few intermediate records, it will vacate few datablock, but still, the High Level Watermark will be the same. The free list [0: no free space / 1: free space available ] will be updated with vacant space in data block having less than 50% data
— > When UPDATE happens for any records, it will use the space available till PCT Free boundary.