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

Bernd

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org 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 IDUG.org member, please register at http://www.idug.org/component/juser/register.html