What is HIGH WATERMARK?I got to know that it is reset when the TRUNCATE command is executed on a table.

  • Interview Candidate
  • 7
  • 10897

Showing Answers 1 - 7 of 7 Answers

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

  Was this answer useful?  Yes

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

  Was this answer useful?  Yes

mariashylaja

  • 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.

  Was this answer useful?  Yes

maria antony

  • Aug 14th, 2006
 

Exactly dear friend. Thanks.

  Was this answer useful?  Yes

g_sidhu

  • 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

  • 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

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions