Informix Online 5.0 Query Optimizer

We have a problem concerning Informix Online 5.0 query optimization. Informix seems to be choosing extremely inefficient paths for no apparent reason. The part_ref table originally had only one index, a composite of partnumber and option. When a query was performed that joined to this table, it took an excessive amount of time. We added another index to the part_ref table, this time only on the partnumber field. When the query that joined to part_ref was run again, Informix then used the original partnumber/option index, and ran in several seconds. This would be understandable if the new optimized query used the new index. However, according to sqexplain.out, it didn't even look at it. Note in the comparison below how the same query was organized differently (refer to the attached sqexplain.out listing for details). Also note that the query recognized and used the underlying table's origdatetime index. Below is a summary of the queries and the sqlexplain.out before and after adding an index on partnumber. 1) Query: select * from sfdmdefect2_vu where origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" Before adding additional index on partnumber (part_ref is indexed by the composite partnumber and option only) - Table Path Index Used --------------- --------------- --------------------- 1 part_ref sequential scan n/a 2 serialpart index partnumber and option 3 gljjtest1 index serialnumber 4 serialcomponent index componentsn 5 serialcomponent index printersn **** note that the gljjtest1.origdatetime column is indexed by isn't used by Informix. 2) Query: select * from sfdmdefect2_vu where origdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" After adding additional index on partnumber (part_ref is indexed by partnumber AND the composite partnumber and option) - Table Path Index Field(s) --------------- --------------- --------------------- 1 gljjtest1 index origdatetime 2 serialpart index serialnumber 3 part_ref index partnumber and option 4 serialcomponent index componentsn 5 serialcomponent index printersn **** note that in this case Informix uses the gljjtest1.origdatetime indexed column (and we are happy...) - Informix engine version 5.0 - Update Statistics was run prior to performing the query ________________________________________________________________________________ --------------------------------DETAILS----------------------------- First QUERY: ----------- select * from sfdmdefect2_vu whereorigdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" Estimated Cost: 2447 Estimated # of Rows Returned: 5 1) informix.part_ref: SEQUENTIAL SCAN 2) informix.serialpart: INDEX PATH select * from sfdmdefect2_vu whereorigdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" (1) Index Keys: partnumber option Lower Index Filter: (informix.serialpart.option = informix.part_ref.option AND informix.serialpart.partnumber = informix.part_ref.partnumber ) 3) informix.gljjtest1: INDEX PATH Filters: (informix.gljjtest1.origdatetime >= '1993-06-01 00:00:00' AND informix.gljjtest1.origdatetime <= '1993-06-01 06:00:00' ) (1) Index Keys: serialnumber Lower Index Filter: informix.gljjtest1.serialnumber = informix.serialpart.serialnumber 4) informix.serialcomponent: INDEX PATH (1) Index Keys: componentsn Lower Index Filter: informix.serialcomponent.componentsn = informix.gljjtest1.serialnumber 5) informix.serialcomponent: INDEX PATH Filters: (informix.serialcomponent.componentsn MATCHES 'HDR*' OR informix.serialcomponent.componentsn MATCHES 'GDR*' ) (1) Index Keys: printersn Lower Index Filter: informix.serialcomponent.printersn = informix.serialcomponent.printersn Second QUERY: ------------ select * from sfdmdefect2_vu whereorigdatetime between "1993-06-01 00:00:00" and "1993-06-01 06:00:00" Estimated Cost: 3583 Estimated # of Rows Returned: 26 1) informix.gljjtest1: INDEX PATH (1) Index Keys: origdatetime Lower Index Filter: informix.gljjtest1.origdatetime >= '1993-06-01 00:00:00' Upper Index Filter: informix.gljjtest1.origdatetime <= '1993-06-01 06:00:00' 2) informix.serialpart: INDEX PATH (1) Index Keys: serialnumber Lower Index Filter: informix.serialpart.serialnumber = informix.gljjtest1.serialnumber 3) informix.part_ref: INDEX PATH (1) Index Keys: partnumber option Lower Index Filter: (informix.part_ref.partnumber = informix.serialpart.partnumber AND informix.part_ref.option = informix.serialpart.option ) 4) informix.serialcomponent: INDEX PATH (1) Index Keys: componentsn Lower Index Filter: informix.serialcomponent.componentsn = informix.gljjtest1.serialnumber 5) informix.serialcomponent: INDEX PATH Filters: (informix.serialcomponent.componentsn MATCHES 'HDR*' OR informix.serialcomponent.componentsn MATCHES 'GDR*' ) (1) Index Keys: printersn Lower Index Filter: informix.serialcomponent.printersn = informix.serialcomponent.printersn ________________________________________________________________________________ create view sfdmdefect2_vu as select dv1.*, pt1.partnumber serialpart, pt1.option serialpartoption, pt1.partgroupid serialpartgroup, pt1.countryid serialpartcntry, pt1.nldescription serialpartdesc, sc2.componentsn engineserial from gljjtest1 dv1, serialpart sp1, part_ref pt1, serialcomponent sc1, serialcomponent sc2 where dv1.serialnumber = sp1.serialnumber and sp1.partnumber = pt1.partnumber and sp1.option = pt1.option and dv1.serialnumber = sc1.componentsn and sc1.printersn = sc2.printersn and (sc2.componentsn matches "HDR*" or sc2.componentsn matches "GDR*") ________________________________________________________________________________ { TABLE "informix".gljjtest1 row size = 1153 number of columns = 43 index size = 60 } crea