Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

HIGH WATERMARK and Gather Statistic

746835Feb 3 2010 — edited Feb 4 2010
Hi Masters,
I am trying to understand the relation between High Watermark and Gather Statistic here.

From my understanding High Watermark is the maximum block that ever been occupied by a table, regardless now the block is empty because rows already deleted.
And the way to fix it is by shrinking the table or truncating the table.

On the other hand Gather statistic is to help Oracle produce the optimum execution plan for the table. In my understanding by performing gather statistic, Oracle will know how many blocks are empty and how many blocks are filled with records, check the indexes and produce the new execution plan.
So Gather Statistic will not fix the High Watermark, but just to let Oracle know if,
for example the table occupied block 1-10, and the filled block is only block 1,3,4, and Oracle need to perform Full Table Scan. they do not need to scan up the way until block 10, just until block 4, because block 5-10 are empty.

Is this the correct understanding? Please kindly advice.

thanks
Lie
This post has been answered by Hemant K Chitale on Feb 3 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 4 2010
Added on Feb 3 2010
10 comments
1,876 views