DB2 LUW: how to influence query planner's choice of join Suppose I have two tables, A and B.  They "share" the same primary key, which is indexed in both tables.  Table B also has a timestamp column which is separately indexed.

I do a subselect on B using the timestamp column (I give it a 24-hour interval for the timestamp to belong to), and inner join the results against A using the primary keys.

A and B are huge (O(10^9) rows).  When I give the subselect a recent 24-hour period, it seems that the statistics are poor and the planner underestimates the number of rows coming out of the subselect (say, anywhere between O(1) and O(10^5) rows, while there are actually 6M such rows for the queries I run).  It picks a nested loop join, with the subselect output as the "outer" table and A as the "inner" table, accessed via its index.  While NL joins have a bad rep, it only takes a few minutes to complete.

When I tell it to look for older timestamps ("old" 24-hour periods), it does understand that there are about 6M rows coming out of the subselect.  It picks a hash join, with A as the outer/probe table, and the subselect output as the inner/build table.  It does a table scan on A as part of the plan.  It doesn't complete even after 48 hours.

Is there a way to force or hint to the planner that it should use the nested loop join?

While I'd be interested in hearing about DB tuning as a solution, this is a box I have no authorization to tune.

...more information:  the join switches from nested loop to hash when the number of rows returned by the subselect increases beyond about 1.012*10^6.
Hash joins require an "equijoin predicate".  So I rewrote the query as an explicit join (instead of IN...(subselect)), and instead of using A.key = B.key as the join condition, I used A.key > B.key - 1 AND A.key &lt; B.key +1.