HIGH WATERMARK and Gather Statistic
746835Feb 3 2010 — edited Feb 4 2010Hi 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