Full Joins - 3+ tables

[BrianTyler]

DB2 v8 on AIX

Table A contains CUSTNO, which is indexed as Unique

Table B contains ORDNO and CUSTNO , both indexed seperately with ORDNO being unique,and CUSTNO being a foreign key

Table C contains ORDNO, CUSTNO and ITEMNO each indexed seperately, with ITEMNO being unique, and CUSTNO and ORDNO being foreign keys.

When joining these three tables, the minimum joins are A.CUSTNO to B.CUSTNO, then B.ORDNO to C.ORDNO.

If any of you have experimented in this area, please share your results.

Thanks

brian
Is there likely to be any benefit in adding further joins such as B.CUSTNO to C.CUSTNO; A.CUSTNO to C.CUSTNO.

I am unsure whether the optimizer would prefer to have these extra joins specified or would it confuse matters and actually slow the query down.

 

[ddiamond]

In the scenerio you described, there would not be any benifit to adding further joins. When you have a one to many relationship, you only need to join parent to child on the parent's primary key and the child's foreign key. The minimum number of joins you listed above accomplishes this.

 

[BrianTyler]

Thanks DD,

This confirms what I thought. By coincidence I have just run a query and got the following response:

SQL0437W Performance of this complex query may be sub-optimal. Reason code:
"3". SQLSTATE=01602

The following expansion of the error message does confirm your answer.

SQL0437W Performance of this complex query may be sub-optimal.
Reason code: "<reason-code>".

Explanation:

The statement may achieve sub-optimal performance since the
complexity of the query requires resources that are not available
or optimization boundary conditions were encountered. The
following is a list of reason codes:


1 The join enumeration method was altered due to memory
constraints

2 The join enumeration method was altered due to query
complexity

3 Optimizer cost underflow

4 Optimizer cost overflow

5 Query optimization class was too low

6 Optimizer ignored an invalid statistic

The statement will be processed.

User Response:

One or more of the following:

o Increase the size of the statement heap (stmtheap) in the
database configuration file. (Reason code 1)

o Break the statement up into less complex SQL statements.
(Reason codes 1,2,3,4)

o Ensure predicates do not over-specify the answer set (Reason
code 3)

o Change the current query optimization class to a lower value
(Reason codes 1,2,4)

o Issue Runstats for the tables involved in the query (Reason
codes 3,4)

o Change the current query optimization class to a higher value
(Reason code 5)

o Reissue RUNSTATS for both the tables involved in the query
and their corresponding indexes, that is, use the AND INDEXES
ALL clause so that table and index statistics are consistent
(Reason code 6)



sqlcode : +437

sqlstate : 01602


Regards

Brian

 

[blom0344]

You should create explain plans for both strategies. This will give some insight in how DB2 resolves the query and give you a cost estimate. I bet the 2 join solution will have a shorter and cheaper plan.

Also consider dft_queryopt..

Ties Blom

 

[MarcLodge]

Brian,

As a general rule, I would code your query:

SELECT DATA 

FROM TABLEA A
    ,TABLEB B
    ,TABLEC C

WHERE B.CUSTNO = A.CUSTNO

  AND C.CUSTNO = A.CUSTNO
  AND C.ORDNO  = B.ORDNO

Many years and many DB2 releases ago, you had to code the B to C join on CUSTNO, but since release 3 or 4 the optimiser does this for you and it is usually omitted.

I have a feeling that the A to C join on CUSTNO may well be unnecessary in terms of efficiency, although I'm not sure about that. I code it the above way due to readability. If you get a large number of tables making multiple joins, it's very easy to make mistakes if you start leaving out those deemed as unnecessary.

I'd be interested to know the results of the explains that Ties suggested.

Regards,

Marc