DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
Readahead prefetching looks ahead in the index to determine the exact data pages and index leaf pages that ISCAN-FETCH and index scan operations will access, and prefetches them.
While readahead prefetching provides all the data and index pages needed during the index scan (and no pages that are not needed), it also requires additional resources to locate those pages. For highly sequential data and indexes, sequential detection prefetching will normally out-perform readahead prefetching.
With smart data and smart index prefetching, both sequential and readahead prefetching is enabled, which is the default. Sequential detection prefetching is initially used until a threshold of non-prefetched pages is reached or, in some cases, if the MAXPAGES estimate made by the optimizer is exceeded. When the threshold of non-prefetched pages is reached or if the MAXPAGES estimate is exceeded, readahead prefetching is enabled.
Use the seqdetect database configuration parameter to control whether the database manager performs readahead or sequential detection prefetching
If index predicates must be evaluated during an index scan, and the optimizer determines that the index predicates for a particular index scan have a compound selectivity rate below 90% (not many rows qualify), data readahead prefetching is disabled for that index scan. Note that this is a compound selectivity taking into account all index predicates for that particular index scan. If the query optimizer enables readahead prefetching for lower predicate selectivities it might cause many unnecessary pages to be prefetched.
Data readahead prefetching is also disabled while scanning a non-partitioned index on a range-partitioned table to prevent a prefetch request from containing page references from multiple partitions.
For smart data and smart index prefetching, which can use readahead prefetching, these prefetching techniques apply only to index scan operations and do not support XML, extended, and Text Search text indexes.