Under most conditions, single-table select operations on a heap use:
The largest I/O available to the table, and
Fetch-and-discard (MRU) replacement strategy
For heap tables, select operations performing large I/O can be very effective. Adaptive Server can read sequentially through all the extents in a table.
See Chapter 1, “Understanding Query Processing” in Performance and Tuning Series: Query Processing and Abstract Plans.
Unless the heap is being scanned as the inner table of a nested-loop join, data pages are needed only once for the query, so MRU replacement strategy reads and discards the pages from cache.
Large I/O on allpages-locked heap tables is
effective only when the page chains are not fragmented. See “Maintaining heap tables”.
