DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL08028"
with level identifier "03090106".
Informational tokens are "DB2 v8.1.0.136", "s070720", "U810953", and FixPak
"15".
Product is installed at "/opt/IBM/db2/V8.1".

Solaris 8

I have a query similiar to:

(1) select col1, col2 from tableA where date between x and y
and col3 in (subselectA)
and col4 in (subselectB)
group by col1

(2) select col1, col2 from tableA where date between x and y
and col3 in (subselectA)
group by col1

(3) select col1, col2 from tableA where date between x and y
group by col1

1 Does not use prefetching for tableA, however both 2 and 3 does. Any ideas why ?

I guess , as per statistics, "col4 in (subselectB)" will reduce the resultset to a small number.