index problem

[helpme]

Hi guys,
I have a table on which yesterday i had around 2 lac deletes.
And the height of indexes had exceded to 5.
Then i rebuilt the index and saw that the deleted leaf rows were less than the total number of leaf rows but the height still remained 5!!!

I dont know if this is normal because i assumed the height would hve dropped ?

Can any one please tell me what might be wrong?

Thank you
Sam

[anandharaj]

Hi Sam,

It sounds like HIGH WATER MARK.

HIGH WATER MARK indicates the level upto which the last table block reached.
As soon as u enter data into the table HIGH WATER MARK also rises.

When u delete the rows from the table the HIGH WATER MARK remains where it is, it doesnot fall.

This leads to more I/O since when Oracle performs scan of the table it scans upto the HIGH WATER MARK.

HIGH WATER MARK is reset only when u truncate or export the table, drop the table and import it again.