1. High water mark is the point upto which data is stored in table as block
2 IT Increases upward when you insert data
3. It decreases downward when you truncate or drop the table not in the case of delete.
Example
A table containing 225 rows with total of 9 blocks in which 6 blocks are empty.Analyse table test1 compute statistics.
Select blocks, empty_blocks, num_rows
From user_tables
Where table_name = ‘test1′;
After deleting some data it is still using same number of blocks.
Alter table test1 move.
Analyse table test1 compute statistics.
Now it frees the blocks in which data is deleted and hwm is decreases downward to this when full table scan is performed then it reads blocks up to hwm which is less this time and it will decreases execution time.
2 IT Increases upward when you insert data
3. It decreases downward when you truncate or drop the table not in the case of delete.
Example
Select blocks, empty_blocks, num_rows
From user_tables
Where table_name = ‘test1′;
After deleting some data it is still using same number of blocks.
Alter table test1 move.
Analyse table test1 compute statistics.
Now it frees the blocks in which data is deleted and hwm is decreases downward to this when full table scan is performed then it reads blocks up to hwm which is less this time and it will decreases execution time.
No comments:
Post a Comment