Re: help on self-outer joins & indexes

>Date: Fri, 23 Jul 93 15:40:00 mdt >From: Gary Jamrog <uunet!hpbs1841.boi.hp.com!jamrog> >Subject: help on self-outer joins & indexes >X-Informix-List-Id: <list.2539> > >We are in need of Informix expertise in two areas: >... >2) performance tuning using self-joins and outer joins. > >2) We have a view that performs a self-outer join. > Example: > > select a.part, b.part, c.part > from failures a, outer failures b, outer failures c > where a.failnum = b.failnum > and a.failnum = c.failnum > and a.code = "OPEN" > and b.code = "ASSESSED" > and c.code = "REPAIRED"> > Does anyone have experience with this type of query? Any performance > tuning tips, specifically in the area of temp table use or indexes on > the underlying tables of a view? The first rule of optimising outer joins is "Don't use them". So is the second, third and fourth rule. The fifth rule is to remember that you won't get the results you thought you were going to get, even when you take into account the fifth rule. There is a feature which means that all the rows in the dominant table which satisfy the criteria on the dominant table will be returned, regardless of filter conditions on the outer-join tables. This is both hard to explain and hard to understand, and it was documented in an ancient Tech Notes (Spring 87 for outer joins; the Tech Notes for Summer 87 discussed self-joins). Why are you using an outer join? What is going to be returned by this view is all the failures which are OPEN; where the failure has been ASSESSED, you will get the ASSESSED b.part, but where there is no ASSESSED row, you will get a NULL for b.part, and similarly, where the failure has been REPAIRED, you will get the REPAIRED b.part, but where there is no REPAIRED row, you will get a NULL for b.part. I doubt if this is what you had in mind, and I suspect that the inner join is what you actually wanted. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>