Optimiser leaves redundant joins in views

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. -- 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/