Fwd: SORTC_JOIN = Y on Nested Loop Join

Bernd Oppolzer

Fwd: SORTC_JOIN = Y on Nested Loop Join
Thank you all, I found the solutin myself.

The PK index on the outer table has a very bad CLUSTERRATIO (52),
so DB2 reads the data by tablespace scan and uses a sort
to prepare the nested loop join.

Kind regards

Bernd



---------- Weitergeleitete Nachricht ----------

Subject: SORTC_JOIN = Y on Nested Loop Join
Date: Thu, 29 Jan 2009 12:14:31 +0100
From: Bernd Oppolzer <[login to unmask email]>
To: "DB2-Liste" <[login to unmask email]>

Hello DB2 list,

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

...

______________________________________________________________________

* 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

Jack Campbell

Re: SORTC_JOIN = Y on Nested Loop Join
(in response to Bernd Oppolzer)
Bernd

the first case I can think of is if the <PK> and <FK> table have different
clustering sequences (or low clusterratios) - DB2 will perform a RID sort to put
the keys in the best order for accessing the outer table in the nested loop of
the join.

This would help performance by matching the RID sequence to the page
sequence of the table been joined to and potentially reduce getpages (and
also potentially enable prefetching)

Regards

Jack

______________________________________________________________________

* 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

Patrick Bossman

Re: SORTC_JOIN = Y on Nested Loop Join
(in response to Jack Campbell)
Hello,
I want to clarify the nested loop join with sort composite access path.

DB2 will optionally sort the outer composite for nested loop join. DB2
usually does this when there are many rows in the outer table and the outer
table is not in inner join key sequence (or DB2 doesn't know that the outer
happens to be in inner join key sequence).

Usually, the inner index is well clustered or there's index only access (for
poorly clustered index, hybrid and sort merge begin to compete favorably
because the data would be accessed sequentially).

DB2 may choose to sort the composite to avoid random IO to the inner index
and data. DB2 will compute the cost of the sort and the lower cost of
accessing the inner sequentially - taking advantage of index lookaside and
sequential detection.

So the cost of no sort with random access to the inner is competed with
nested loop join with a sort composite*.

* If DB2 can tell that the outer and inner are roughly or precisely in the
same sequence, it will recognize index lookaside and sequential detection
even without sort composite.

Best regards,
Pat Bossman

______________________________________________________________________

* 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