High-water mark
High-water mark indicates the point up to which table or index has ever contained data.Let me explain .Suppose we create an empty table,the high-water mark would be at the start of the table segment
Now if we insert some data into the table segment, the HWM moves and it will be at position up to which the data is in the segment
If insert more data in the table segment, HWM moves further to point the position up to which the data is in the segment
Now if we delete the data from the table see the new position of HWM
As we can see above on deleting the data, HWM does not move. The drawback of this is that oracle always read the blocks up to high water mark in case of full table scan. You must have noticed that doing a count (*) on empty table, takes time to show you no rows. The reason for taking so much time is setting of HWM at higher position.
Now you must be thinking , how to set the high-water mark at lower position ?
The only method to set the HWM is to truncate a table.
Let us see how truncate set the HWM.
HWM is reset now , after truncating data. Table where is having lots of delete or insert operation going on, probably has High HWM. If the HWM is high, it is always advised to rebuild table segment for performance improvement.
High-water mark indicates the point up to which table or index has ever contained data.Let me explain .Suppose we create an empty table,the high-water mark would be at the start of the table segment
Now if we insert some data into the table segment, the HWM moves and it will be at position up to which the data is in the segment
If insert more data in the table segment, HWM moves further to point the position up to which the data is in the segment
Now if we delete the data from the table see the new position of HWM
As we can see above on deleting the data, HWM does not move. The drawback of this is that oracle always read the blocks up to high water mark in case of full table scan. You must have noticed that doing a count (*) on empty table, takes time to show you no rows. The reason for taking so much time is setting of HWM at higher position.Now you must be thinking , how to set the high-water mark at lower position ?
The only method to set the HWM is to truncate a table.
Let us see how truncate set the HWM.
HWM is reset now , after truncating data. Table where is having lots of delete or insert operation going on, probably has High HWM. If the HWM is high, it is always advised to rebuild table segment for performance improvement.
Krishna - Nice Explanation. If possible update it. with oracle 10g. we have segment shrink. instead of truncate.
ReplyDeleteI always love~~ an explanation with picture.
ReplyDeleteCan i ask a question?
If we insert data after when HWM already exist, where will the data be inserted?
After the data (red block)?
Or after the HWM (green block)?
Nice explanation, but HWM can also be reset by reorganizing the table ( ALTER TABLE MOVE ), or using the SHRINK commands of ORACLE 10g.
ReplyDeleteGreat read tthank you
ReplyDelete