Is this a Bug??? Opinions wanted
Below is the set explain output from two very similar queries. Please
look carefully at the SQL. These queries do return different results.
Should they? We have differing opinions within our database team.
To complicate matters even more, if we change the setting of
OPTCOMPIND, the query results change in the first query example to
match those in the second query example. This happens when
OPTCOMPIND=0.
We are currently running Informix OnLine 7.11.uc1 on a SparCenter
2000E with Solaris 2.4. The database is approximately 70 gig with
informix mirroring. We extensively fragment our tables and indexes,
and many indexes are detached.
Any and all opinions are welcome.
QUERY:
------
select *
from item_master im, outer item_xref ix
where ix.item_number = "02124"
and im.item_number = ix.item_number
Estimated Cost: 58582
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) informix.im: SEQUENTIAL SCAN
2) informix.ix: INDEX PATH
Filters: informix.ix.item_number = '02124'
(1) Index Keys: item_number sales_category
Lower Index Filter: informix.ix.item_number =
informix.im.item_number
QUERY:
------
select *
from item_master im, outer item_xref ix
where im.item_number = "02124"
and im.item_number = ix.item_number
Estimated Cost: 69
Estimated # of Rows Returned: 1
Maximum Threads: 1
1) informix.im: INDEX PATH
(1) Index Keys: item_number
Lower Index Filter: informix.im.item_number = '02124'
2) informix.ix: INDEX PATH
(1) Index Keys: item_number sales_category
Lower Index Filter: informix.ix.item_number =
informix.im.item_number