To provide some context, can you explain the motivation for this "UNION"-construct? I recall hearing Informix has problems handling "OR" constructs, is this meant to adress that? Please provide more details.
The history of UNION queries ...
Once upon a time back in MMBase 1.6 we've suffered quite heavy performance problems that had to do with the OR's in the relationconstraints that were generated by the clusterbuilder, i've made a patch for the clusterbuilder that turned the OR into UNIONS to make the queries faster. That helped.
To make use of the new storage-layer in mmbase 1.7 i've copied the OR --> UNION conversion to the storage-layer. But for more and more "features" (like max, offset, order by and now group by) support had to be written. But writing this kind of support for UNION-query's is quite complicated (imho too complicated).
What the exact reason was that the relational contraints based on a "OR" were too heavy for informix is unknown. We have checked our indexes and UPDATE STATISTICS, they seem all OK. We probably have to investigate why the OR's are extreme slow, and might find a solution at the database side.
Regards,
Mark Huijser - Developer EO-Technology
