Some of the things that can cause fluctuating filtering are predicates with ranges that vary, sometimes returning a small subset of rows and sometimes returning everything. You know the type, perhaps there is a BETWEEN clause that can be set and sometimes it is set as BETWEEN 3 AND 5, whereas other times it is set as BETWEEN 0 and 999999. And maybe even sometimes it is set to BETWEEN 3 AND 3 to just search for equality... Or perhaps it is a LIKE clause that sometimes starts with a wildcard ('%').
Well, Db2 12 offers execution time adaptive indexes that allows list-prefetch plans to quickly determine filtering and adjust at execution time as needed. Db2 can do this for static SQL queries even if REOPT(ALWAYS) is not specified.
Execution time adaptive indexes are not limited to search screening, as described in the previous paragraph. Indeed, any query with a high uncertainty in the optimizer’s estimate can benefit. This includes range predicates, JSON, Spatial, and index on expression queries.
A quick evaluation is performed by looking done at the literals used in the query. Further costlier evaluation of filtering is deferred until after one RID block is retrieved from all participating indexes. This offers a better optimization opportunity while at the same time minimizing overhead for short running queries.
If you are interested in tracking when adaptive index processing is utilized, IFCID 125 has been enhanced to track this feature.
