Overrides

If prefetching is turned on for a table or an index, you can override the prefetching for a session with set prefetch "off". If prefetching is turned off for an object, you cannot override that setting.

The prefetch, lru, and mru options to the select, delete, and update commands suggest the I/O size and cache strategy for individual statements. If prefetching or MRU strategy is enabled for a table or an index, you can override it for a query by specifying I/O the size of the logical page size for prefetch, and by specifying lru strategy. For example, the following command forces LRU strategy, logical page size I/O, and a table scan of the titles table:

select avg(advance) 
from titles (index titles prefetch 2 lru)

If you request a prefetch size, and the object’s cache is not configured for I/O of the requested size, the optimizer chooses the best available I/O size.

If prefetching is enabled for an object with sp_cachestrategy, using a prefetch specification of the logical page size in a select, delete, or update command overrides an earlier set prefetch "on" statement. Specifying a larger I/O size in a select, delete, or update command does not override a set prefetch "off" command.