SORTC_JOIN = Y on Nested Loop Join

Bernd Oppolzer

SORTC_JOIN = Y on Nested Loop Join
Hello DB2 list,

we encountered some performance problems on a join of
two large tables (80 million records both).

The two tables are connected by a PK-FK-relation,
which should be 1:1 in almost all cases.

The table with the PK (tab1) is taken as the outer table,
the other (tab2) as inner (although there is a local
condition for tab2).

Maybe wrong statistics. But I don't understand the following:

there is a Y in the column SORTC_JOIN in the second row
of the PLAN_TABLE (the row containing METHOD = 1).

Under what circumstances will DB2 sort the composite in
a nested loop join? There is no List Prefetch on the
inner table. What does the Y in SORTC_JOIN mean?

Kind regards



* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________ was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an member, please register at