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

10545,L6Q Apparently they are having problems reproducing. I actualy sent them a copy of the problem table. Date: Thu, 12 May 2011 23:21:05 +0100 Subject: Re: Why does this transaction on my IDS 11.70FC2 freeze it up? From: domusonline@gmail.com To: tmroy@hotmail.com CC: informix-list@iiug.org Can you post the PMR number? And you should ask that to support. An optmizer directive can be an acceptable temporary workaround, but if the problem can affect several queries it's not practicable. And of course, if it's a bug there must be a fix... On Thu, May 12, 2011 at 6: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 whereanml_sire_key = 12345 or anml_dam_key = 5678 QUERY: (OPTIMIZATION TIMESTAMP: 04-23-2011 08:32:30) ------ select count(*) from orhanmle whereanml_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 -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...