Re: Optimiser leaves redundant joins in views

Irwin Goldstein wrote: > > Peter Lancashire <Peter.Lancashire.PL1@bayer.co.uk> wrote in article > <35DA98FB.26B6@bayer.co.uk>... > > As nobody commented on my previous posting on this, I'll try again with > > some more information gleaned from further work. > > > > I have a view which joins several tables. When I do a query in which the > > select list and the when clause logically require only a subset of the > > joins, the optimiser just goes ahead and does the redundant joins > > anyway, according to the SET EXPLAIN output. This, to put it gently, > > reduces the usefulness of views. > > > > Is the optimiser just sub-optimal or can I do anything about this? I am > > using SE 7.12 but will upgrade to SE 7.23 when tech support get around > > to it... I do a weekly UPDATE STATISTICS LOW. All joins are by index > > paths. I have SET OPTIMIZATION HIGH. Does the optimiser do more > > optimising in later versions? > > > > This problem also arises with 4GL CONSTRUCT statements. One has the > > choice of writing a full SELECT statement to cover all the join > > possibilities implied in the CONSTRUCT. Or, you can laboriously analyse > > the constructed where clause and build a minimal SELECT. I have a > > 68-field construct covering 8 or so tables, most of them aliased several > > times. Asking the optimiser to do some work is an attractive option. > > Actually, the "extra" joins are still necessary to make the view return > consistent results, unless they are all outer joins. It may be that the > joins to tables with columns not selected will eliminate some rows from the > result. If the join is not performed you may get more rows back. A view > which returns more or less rows for the same where clause dependent on the > columns selected would most likely cause unexpected results. > > Irwin Goldstein > Objective Software Systems, Inc. > http://www.objectsoft.com Yes, but the optimiser leaves redundant outer joins in place. Also, it should be able to figure out that the table referenced by a NOT NULL column with a REFERENCES constraint could be eliminated as the join will always succeed and return only one joined row. Maybe this is asking too much? Thanks also to Andreas Zeugswetter for replying by mail with a similar point and the thought about the foreign keys. -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions AND the release notes. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/