Full table scan – high water mark

Posted on

Question :

When doing a full-table scan, why does Oracle scan all the rows beneath the high-water mark? Doesn’t it know that some of the rows have been deleted and that they shouldn’t be scanned?

Answer :

Oracle (as well as any other RDMS) doesn’t scan “rows”. It operates with blocks (other vendors may use different terminology , e.g. page) , and it doesn’t know which blocks have or don’t have rows. Also, sequential read is much faster than random , and it’s way cheaper to read more than needed into memory… A good graphical explanation of HWM : http://chandu208.blogspot.ca/2012/02/high-water-mark-hwm.html

Leave a Reply

Your email address will not be published. Required fields are marked *