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

Does it assert fail?
You should open a PMR since that's not a normal situation...
Regards.

On Sat, Apr 23, 2011 at 1:37 PM, Todd Roy <tmroy (AT) hotmail (DOT) com> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

It sure doesn't. I will Monday, thanks.

Date: Sat, 23 Apr 2011 20:17:06 +0100
Subject: Re: Why does this transaction on my IDS 11.70FC2 freeze it up?
From: domusonline (AT) gmail (DOT) com
To: tmroy (AT) hotmail (DOT) com
CC: informix-list (AT) iiug (DOT) org

Does it assert fail?

You should open a PMR since that's not a normal situation...

Regards.

On Sat, Apr 23, 2011 at 1:37 PM, Todd Roy <tmroy (AT) hotmail (DOT) 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 (AT) iiug (DOT) 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...

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 (AT) hotmail (DOT) 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 (AT) iiug (DOT) 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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

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 (AT) hotmail (DOT) com> wrote:

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 (AT) hotmail (DOT) com> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

10545,L6Q
Apparently they are having problems reproducing. I actualy sent them a copyof 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 (AT) gmail (DOT) com
To: tmroy (AT) hotmail (DOT) com
CC: informix-list (AT) iiug (DOT) 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 (AT) hotmail (DOT) 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 (AT) hotmail (DOT) 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 (AT) iiug (DOT) 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 (AT) iiug (DOT) org
http://www.iiug.org/mailman/listinfo/informix-list

_______________________________________________

Informix-list mailing list

Informix-list (AT) iiug (DOT) 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...

On May 12, 6:45*pm, Todd Roy <tm... (AT) hotmail (DOT) com> wrote:
Just out of interest have you got PDQ enabled for that session?

Nope