High Water Mark is applicable to the Segments or even we can say DB blocks (at granule level) attached to the database table. This indicates the highest level up to, which the space occupied in the blocks by the table data.
This can be illustrated with a simple example from our day-to-day life.
You might have seen a glass filled half with milk. Now, the level where the milk is available in the glass is the high water mark. Even if you pour out some milk out of the glass still the mark will be there. This indicates that at one time the milk was filled up to that level.
Similarly, in the Oracle database high water is the level, which indicates the last block that held data.
Let’s say when a table gets created the following of DB blocks gets associated to it.Now there is no data into the table so, the high water mark will be set at the first block.When the data is populated into the table then the HWM is set to the DB block up to, which the data is stored.When the few rows gets deleted from the table Still the HWM remains at the last level only. This is because the HWM is not reset after the deletion of rows. This may result in two major problems:
1) During the full table scan, Oracle always scans the segment up to the level of
HWM. If we don’t have the data in those blocks then the time spends in scanning those block is useless.
2) These blank blocks will not appear in the Freelists of the database so, when new rows will be inserted with Direct path using APPEND hint or SQL*Loader direct path the data will be stored in the free blocks above HWM only. Therefore the empty blocks below the HWM will be wasted.
How to reset High Water Mark?
If we execute the “Truncate table” command then the HWM gets reset automatically.
In Oracle 9i and below, you can use the “ALTER TABLE….MOVE….” command to reset the HWM and use the empty block effectively. In Oracle 10g release this has become more effective. Now, you can “Shrink” tables, segments and indexes to use the space wasted due to HWM not reset but and make them available to database for other use. Your tablespace must have “ASSM (Automatic Segment Space Management)” enabled before you shrink them. We will look into both the options one by one.
In Oracle 9i and below, the space resetting is done by “ALTER TABLE….MOVE….” command in the following way:
Syntax:
Alter table
Alter table t1 move storage (initial 10K next 10K) tablespace new_tablespace;
In case you don’t want to move storage to a different tablespace then you can use the command as:
Syntax:
Alter table
Alter table t1 move storage (initial 10K next 10K);
There are certain restrictions on doing so. Which are as follows:
1) Table should not have LOB, LONG or LONG RAW columns.
2) Entire partitioned table cannot be moved. Individual partition/sub-partition has to be moved separately.
3) Indexes associated to the table will beocme invalid after move so, rebuild the index using “alter index rebuild”.
4) This cannot be done online. The table has to be making unusable before moving.
In Oracle 10g release1, a new feature added to this process. It is called segment shrinking. Segment shrinking is allowed only to those segments that use “Automatic Segment Space Management (ASSM)”. This means the tablespace should be enabled with ASSM feature. Following are the steps to use HWM re-setting:
1) Before we start shrinking segments we need to tell Oracle to re-set the rowIDs of these rows by issuing the following command:
Alter table
2) Now, we are ready to shrink the segment using:
Alter table
Alter table
In this case:
1) We don’t need to do it offline.
2) Indexes will not become invalid or need not to re-create the indexes.
