Re: Why does this transaction on my IDS 11.70FC2 freeze it up?

You could use an external directive for this query and similar ones. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, May 12, 2011 at 1:45 PM, Todd Roy <tmroy@hotmail.com> wrote: > > IBM finally got back to me and told me it was a probabe defect and as a > work-around > told me to turn off MULTI_INDEX with an optimizer directive. > ie: > > SELECT {+AVOID_MULTI_INDEX(orhanmle)} > count(*) > from orhanmle > where anml_sire_key = 743101 > or anml_dam_key = 743101; > > worked great. > > Is there any way I can disable it globally? Ie no code changes. > > > > On Sat, Apr 23, 2011 at 1:37 PM, Todd Roy <tmroy@hotmail.com> wrote: > > Given a table with about 28Million rows, I try to do > a simple transaction : > > set explain on;> > select count(*) from orhanmle where> anml_sire_key = 12345 > or > anml_dam_key = 5678 > > QUERY: (OPTIMIZATION TIMESTAMP: 04-23-2011 08:32:30) > ------ > select count(*) from orhanmle where> anml_sire_key = 12345 > or > anml_dam_key = 5678 > > > Estimated Cost: 8 > Estimated # of Rows Returned: 1 > > 1) informix.orhanmle: MULTI INDEX PATH (SKIP SCAN) (Key-Only) > > (1) Index Name: informix.orhanmle_dkey_i2 > Index Keys (Detached): anml_dam_key (Serial, fragments: ALL) > Lower Index Filter: informix.orhanmle.anml_dam_key = 5678 > OR > (2) Index Name: informix.orhanmle_skey_i3 > Index Keys (Detached): anml_sire_key (Serial, fragments: ALL) > Lower Index Filter: informix.orhanmle.anml_sire_key = 12345 > > > Basically the engine completely freezes up, requiring a onclean -k > to restart it. > > The table has had its statistics freshly rebuilt with Art Kagel's > dostats. I also dropped and recreated all the indexes. > > In fact. This occurs with ANY "or" in the where clause. > > On our production server (11.50FC5) this transaction and others > like it are instantaneous! > > Thanks, > > Todd > > > > > > Signature > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > _______________________________________________ Informix-list mailing list > Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > >