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

On May 12, 6:45 pm, Todd Roy <tm...@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 <tm...@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-l...@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-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list Just out of interest have you got PDQ enabled for that session?

Nope > From: JonRitson@Sky.Com > Subject: Re: Why does this transaction on my IDS 11.70FC2 freeze it up? > Date: Fri, 13 May 2011 08:21:32 -0700 > To: informix-list@iiug.org > > On May 12, 6:45 pm, Todd Roy <tm...@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 <tm...@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-l...@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-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list > > Just out of interest have you got PDQ enabled for that session? > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list