Prefetch size and replacement strategy

This section includes changes to the Performance and Tuning Guide on the information for prefetch and replacement strategy.

In earlier versions, when a prefetch was done you could only specify one size, for example, 2K, 4K, and it had to be the same size for the table scan and possible index scan. This also applied to the replacement strategy.

In Adaptive Server 12.5, you can name more than one size for the table, the index, and the lru/mru. This example shows two prefetch sizes and two replacement strategies for the same table:

Example 1

(i_scan i1 t1) (prop t1 (prefetch 8 16)(lru mru))

This allows you an 8K prefetch for table t1 and a 16K prefetch for any index scan for t1 (i1 in the example). It also allows you an lru replacement strategy for t1 and an mru for any index scan as above.

These changes also apply to the force index syntax in SQL, so the following query has the same effect:

select * from t1(i1 (prefetch 8 16)(lru mru))