Db2 8.1.5 on Windows 2000

I'm looking for help with problems with very slow running queries. These have been narrowed down to an issue with OUTER JOINs.

For example,
FROM
...
TAB1
LEFT OUTER JOIN TAB2 ON TAB1.COL1 = TAB2.COL2,
...
where there are indexes on both TAB1.COL1 and TAB2.COL2
The query can be speeded up (though it loses rows) by changing this to
FROM
...
TAB1
INNER JOIN TAB2 ON TAB1.COL1 = TAB2.COL2,
...

The performance difference is a factor of around 20. I have looked at the explain plans for the 2 versions and the difference is that when the LEFT OUTER JOIN is used, a table scan of TAB2 is performed whereas for the INNER JOIN, the index on TAB2 is used.

The problem does not relate just to this one query but seems to be inherent in the use of OUTER JOINs. Do you know of any way we can tune DB2 so that is uses indexes to process OUTER JOINs or is just the way the database works?

Newbie

left outer join most of the time read the full outer table in full table scan mode. so increasing the prefetch size and other config parameter
like IO* agents will help. similarly table of that sort ishould have been striped in multiple containers and in their own buffer pool is also helpful.
Similarly applying the predicate to the outer join table will decrease the no of
rows.
But rewriting of the query is the most helpful.

regards,

mujeeb

You might want to try to execute runstats with distribution on all columns (or at least key columns), and make use “indexes all” also.

If that does not work, try to increase the query optimization level to 7 (instead of default of 5). Increasing query optimization level has no runtime impact on statically bound packages that are bound ahead of time, and a slight impact on dynamic SQL.

Outer joins will NEVER exclude any rows, so the join is always going to involve a full table scan.

To tune your queries, you will have to reduce the number of rows that will be involved in the outer join (by filtering rows in a prior, inner join or possibly outer joining a nested select).

Post a complete example of a query that is causing problems and I'm sure it can be tuned by someone here to perform satisfactorily.

Damian

Thank you all for valuable suggestions. Here is a sample query:

select
B.LAST_NAME,
B.FIRST_NAME,
D.NAME,
C.CITY,
C.STATE_PROVINCE,
B.BUSINESS_PHONE,
B.BUSINESS_PHONE_EXT,
F.S_LYR_TOT,
F.A_CURR_TOT,
F.S_YTD_TOT,
A.CONTACT_ID,
B.CONTACT_ID,
C.CONTACT_ADDRESS_ID,
D.FIRM_ID,
E.OFFICE_ID,
F.CONTACT_ID
from
DPS.REP_PROFILE A
inner join DPS.CONTACT B on ( A.CONTACT_ID = B.CONTACT_ID )
left outer join DPS.CONTACT_ADDRESS C on ( B.ADDRESS_1_ID = C.CONTACT_ADDRESS_ID )
left outer join DPS.FIRM D on ( A.FIRM_ID = D.FIRM_ID )
left outer join DPS.OFFICE E on ( A.OFFICE_ID = E.OFFICE_ID )
left outer join DPS.REP_CONSOLE F on ( A.CONTACT_ID = F.CONTACT_ID )
where
C.COUNTRY_ID = '0001'
order by
B.UPPER_LAST_NAME asc,
B.UPPER_FIRST_NAME asc with UR;

Newbie

Is UNION ALL a better choice?

Thanks in Advance.

Newbie

I reckon that you could repace one of the outer joins in your query with an inner join. CONTACT_ADDRESS is the null supplying table in an outer join, but you are only interested in records where the country id from this same table is '0001'.

Thank you Damian. Actually it was just a sample query. I need to build queries similar to that. I noticed that "UNION ALL" does seem to be helpful.

Thanks,
Newbie