Interested to Buy Any Domain ? << Click Here >> for more details...
Answer Posted / p.rajasekar
High-water mark is an indicator or pointer up to which
table or index has ever contain data. Let us illustrate
this statement as Suppose we create an empty table , the
high-water mark would be at the beginning of the table
segment.
After inserting data in the table segment , the HWM moves
and point the position up to which the data is in the
segment.By inserting more data in the table segment, HWM
moves further to point the position up to which the data
is in the segment.
------- DATA-----------------
Un-Used Blocks
Now let us delete the data and see the pointer of HWM
DATA
------- Empty Blocks Un-Used Blocks
Full Table
Scan
As you seen above by deleting the data , HWM does
not move. The main disadvantage of this is that oracle
always read the blocks up to high water mark in case of
full table scan . You may have ever notice that doing a
count(*) on empty table , takes time to show you 0 rows.
The reason for delay is setting of HWM at higher position.
Now the question arises in front of us , how we set the
high-water mark at lower position ?
The only way to set the HWM is to truncate a table.
Let us see how truncate set the HWM.
No data in the segment
HWM is reset now , after truncating data.
Table where lots of deletion or insertion takes place ,
probably has High HWM. If the HWM is high , it is better
to rebuild table segment for performance sake.
| Is This Answer Correct ? | 5 Yes | 0 No |
Post New Answer View All Answers
Which column of the user triggers data dictionary view displays the database event that will fire the trigger?
How do I partition in sql?
What is compiled query?
What is sap sql anywhere?
What is an intersect?
What are few of the schema objects that are created using PL/SQL?
What is difference between db2 and sql?
how to check server status with 'mysqladmin'? : Sql dba
Does oracle use sql?
What is a .db file?
How can we solve sql error: ora-00904: invalid identifier?
Why use subqueries instead of joins?
How to add a column ‘salary’ to a table employee_details?
What does select count (*) mean in sql?
What are the usages of sql?
