Specifies the I/O size to use for the scan of a stored table.
( prop table_name ( prefetch size ) )
is the table to which the property is to be applied.
is a valid I/O size: 2, 4, 8 or 16.
select * from t1
( prop t1
(prefetch 16 )
)
16K I/O size is used for the scan of t1.
The specified I/O size is used in the resultant query plan if a pool of that size exists in the cache used by the table.
Partial plans can specify scan properties without specifying other portions of the query plan.
If large I/O specifications in a saved plan do not match current pool configuration or other options:
If the plan specifies 16K I/O, and the 16K pool does not exist, the next largest available I/O size is used.
If session or server-level options have made large I/O unavailable for the query (set prefetch for the session, or sp_cachestrategy for the table), 2K I/O is used.
If you save plans that specify only 2K I/O for the scan properties, and later create large I/O pools, enable replace mode to save the new plans if you want these plans to use larger I/O sizes.
