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?
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