Collocation of Left Outer Joins

[ibadba]

Hello all, today I think I'm stupid, can anyone confirm??

I have a query on multinode DB2 ( v8.2 Aix ), as follows:

select a.col1,b.col1,c .col1
from
table1 a
left outer join
table2 b
on
a.partkey=b.par tkey
left outer join
table3 c
on
b.partkey=c.par tkey

Only if I explicitly add "and a.partkey=c.par tkey" to the last join can
I guarantee collocation ( but this is a generated query ).

Explain plan follows - in this instance it does a DTQ which is ok, when
I do the real , more complex query, the Optimizer decides to BTQ one
side ( which ends up causing lots of problems ) . I don't understand
why the DTQ in 4) is necessary, all tables are partitioned by the same
not nullable col. with same datatype and nodegroup.

thanks for any enlightenment.. .

Query Degree: 1

Rows
RETURN
( 1)

Cost
I/O
|
84
DTQ
( 2)
150.376
3.00515
|
1
NLJOIN
( 3)
119.439
3.00515
/-------------+-------------\
1 3.55306e-18
DTQ FETCH
( 4) ( 9)
91.9691 27.4678
2 1.00515
| /----+---\
1 0.00514551 10142
NLJOIN IXSCAN TABLE: MYSCHEMA
( 5) ( 10) C
54.9138 27.3002
2 1
/-------+-------\ |
1 6.35342e-05 10142
IXSCAN FETCH INDEX: MYSCHEMA
( 6) ( 7) C_INDEX
27.3049 55.2222
1 2.09861
| /----+---\
1512 0.0986051 206175
INDEX: MYSCHEMA IXSCAN TABLE: MYSCHEMA
A_INDEX ( 8) B
52.6759
2
|
206175
INDEX: MYSCHEMA
B_INDEX