Informix join order seems to be influenced by any presence of Left Join

[t***@gmail.com]

howdy all,

we have run across a situation which seems to show that the presence of a Left Join in a query causing Informix to force the join order of all tables to be literally the same as the order they are referenced in the join predicates.

The tables being Inner Joined are in a different order in each example and the explain follows that order exactly.

When removing the Left Join, then Informix chooses its own order for the joins, regardless of the order in the queries.

Does this sound correct?


(i have examples from the visual explain if anyone wants to view them)


thanks,
tom

[Eric Vercelletto]

Post by t***@gmail.com
howdy all,
we have run across a situation which seems to show that the presence of a Left Join in a query causing Informix to force the join order of all tables to be literally the same as the order they are referenced in the join predicates.
The tables being Inner Joined are in a different order in each example and the explain follows that order exactly.
When removing the Left Join, then Informix chooses its own order for the joins, regardless of the order in the queries.
Does this sound correct?
(i have examples from the visual explain if anyone wants to view them)
thanks,
tom

Hi Tom,

someone from the lab should answer this question. Not sure this is expected though...

Just checking:
1) which version is this
2) are your statistics up to date ?
3) have you tested the behaviour using 'set optimization low/medium/high' ?

[Eric Vercelletto]

Post by t***@gmail.com
howdy all,
we have run across a situation which seems to show that the presence of a Left Join in a query causing Informix to force the join order of all tables to be literally the same as the order they are referenced in the join predicates.
The tables being Inner Joined are in a different order in each example and the explain follows that order exactly.
When removing the Left Join, then Informix chooses its own order for the joins, regardless of the order in the queries.
Does this sound correct?
(i have examples from the visual explain if anyone wants to view them)
thanks,
tom

ANd 4: did you create a PMR for this?

[t***@gmail.com]

1) 11.7.FC6
2) yes
3) i have not tried the high versus low
4) that was my next step

thanks!