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 – Full Table Scan and High Water Mark
deleteoracle
Related Question
- Delete rows in parent table based on criteria in child table – Oracle
- Oracle – Using Index on Partitioned Table When Full Scan is Better
- Slow running Oracle query caused by unnecessary full table scan
- MySQL 5.6 – High Disk I/O After Batch Deleting from InnoDB Table
- PostgreSQL 9.5.2 – Resolving Slow DELETE Operations
- Oracle – High Logical Reads Analysis
- SQL Server 2016 – Optimizing Single-Row DELETEs with Foreign Keys
Best 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