SEO

If I set NO_SUBQF=1 then select works correctly still in Linux IDS 9.40.UC6
I don't find any documentation about this environment variable. How does this variable work ?

-----Messaggio originale-----
Da: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]Per conto di
matthias.butterweck@comline.de
Inviato: venerdì 3 marzo 2006 8.43
A: informix-list@iiug.org
Oggetto: Re: R: Problems using "EXISTS"


Thanks you for replying!

So far the problem only occurs on one server with version 9.40.FC3XF.
The query runs ok on another server with the exact same version
9.40.FC3XF and on another one with version 9.21.FC4.

> Clearly, the query can readily be written without an EXISTS clause -
> and this may provide a temporary workaround.

Unfortunately it doesn't, because in our real life application there
may be very many item rows with key="Key1" and value="Value1", and we
only need the distinct child.id_folder values. If we add "DISTINCT" to
the select list, the query runs forever (well, almost).

Upon further investigation we found out that by setting the environment
variable NO_SUBQF, the problem goes away.
Looking at the explain plan of the query without NO_SUBQF we find

4) c0111.item: INDEX PATH (First Row)

Filters: (c0111.item.value = 'Value1' AND c0111.item.key = 'Key1'
)

We think that maybe there lies the problem (bug in the optimizer)

In conclusion the problem is solved somehow by setting NO_SUBQF, but we
have to do this now in all our application environments for all our
customers until the bug is fixed.

Kind regards
Matthias

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

> If I set NO_SUBQF=1 then select works correctly still in Linux IDS
9.40.UC6
> I don't find any documentation about this environment variable. How does
this variable work ?

I understand that this variable turns off a feature in the query optimizer.
With this feature turned on the optomizer tries to flatten certain
subqueries (turns them into a join).
Apparently this is not working in every case.
Here's a short description of the variable:

http://www-1.ibm.com/support/docview...id=swg21173572

Matthias

There's a pervasive bug in this area. It's been re-introduced in 10.0xC4,
although the behaviour is arguably better - you get an assert fail. Fixed
in 10.0xC5 apparently, all in an interim release of 10.0xC4 available next
week. Disabling sub-query flattening with a variable is a workaround.

"Badii Alessandro" <A.Badii@centrosistemi.it> wrote in message
news:mailman.23.1141382044.18205.informix-list@iiug.org...
If I set NO_SUBQF=1 then select works correctly still in Linux IDS 9.40.UC6
I don't find any documentation about this environment variable. How does
this variable work ?

-----Messaggio originale-----
Da: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]Per conto di
matthias.butterweck@comline.de
Inviato: venerdì 3 marzo 2006 8.43
A: informix-list@iiug.org
Oggetto: Re: R: Problems using "EXISTS"


Thanks you for replying!

So far the problem only occurs on one server with version 9.40.FC3XF.
The query runs ok on another server with the exact same version
9.40.FC3XF and on another one with version 9.21.FC4.

> Clearly, the query can readily be written without an EXISTS clause -
> and this may provide a temporary workaround.

Unfortunately it doesn't, because in our real life application there
may be very many item rows with key="Key1" and value="Value1", and we
only need the distinct child.id_folder values. If we add "DISTINCT" to
the select list, the query runs forever (well, almost).

Upon further investigation we found out that by setting the environment
variable NO_SUBQF, the problem goes away.
Looking at the explain plan of the query without NO_SUBQF we find

4) c0111.item: INDEX PATH (First Row)

Filters: (c0111.item.value = 'Value1' AND c0111.item.key = 'Key1'
)

We think that maybe there lies the problem (bug in the optimizer)

In conclusion the problem is solved somehow by setting NO_SUBQF, but we
have to do this now in all our application environments for all our
customers until the bug is fixed.

Kind regards
Matthias

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list