Re: Is this a Bug??? Opinions wanted
barryleb@atl.mindspring.com (Barry Leb) wrote:
>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.
Sorry folks, I'm just a little confused today. Changing this
parameter solved another problem regarding performance and Dynamic
Hash joins so please ignore this part.(If anyone is interested in this
problem, say so and we'll start a new thread.)
>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