Re: Optimiser leaves redundant joins in views

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