Supraja

• Jun 22nd, 2006

 

All Oracle segments have an upper boundary containing the data within the segment. This upper boundary is called the "High Water Mark" or HWM", which means table cannot contain data beyond the HWM

AyanM

 Profile Answers by AyanM 

• Jul 11th, 2006

 

Just to add more when Truncate command is issued it reduces the High Water Mark - the same can be simulated in case of delete also using

alter table <table_name> move;

Some time select query takes a lot of time but can't find any data - this is also because of HWM without any data created by a delete statement

mariashylaja

 Profile 

• Aug 12th, 2006

 

Just to add more on this...

HWM is the upper boundary of the storage area of a table. when TRUNCATE is used on the table, all the rows of that table is deleted and the HWM is decreased to the minimum size. But when DELETE is issued on the table, the rows are deleted and nothing is done on the HWM. Thats is, the HWM is never decreased even after the no of rows for the table is less or none. Thats is this keeps some space always with its own.

Whats the other reated issue on this?.... when your query perform full table scan on the table on which the DELETE is done, the query will scan the disk space till the HWM ( even if there is no row ) which will result in bad response time.... But if TRUNCATE is used, the HWM willl be reset and the query will take less time...

- Maria Antony

Sphurti

• Aug 14th, 2006

 

Just to correct my friend's answer to some extend,  HWM is basically the max space a particular table as occupied till that pt. that doesn't mean that one cannot insert records more than a HWM. Once HWM is crossed a new HWM is set for a table.

maria antony

• Aug 14th, 2006

 

Exactly dear friend. Thanks.

g_sidhu

 Profile 

• Feb 1st, 2008

 

Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.You cannot release space below the high water mark (even if there is no data in the space you want to deallocate). However, if the segment is completely empty, you can release space using the TRUNCATE ... DROP STORAGE statement.

Sudhir Sorout

 Profile Answers by Sudhir Sorout 

• Feb 4th, 2008

 

yes when we truncate instead of delete, for deleting records , truncate set the HWM to 'zero' but if we use only delete then HWM remain same ......

Sudhir Sorout