On Wed, 26 May 2004 06:41:56 -0400, Fernando Nunes wrote:
> Dragi Raos wrote:
>> Never mind. All I wanted to say is that I encountered a class of SQL
>> statements (of which this one was only superficially representative) which
>> tend to run an order of magnitude slower on Informix compared to Oracle (to
>> my great surprise), on very small database with identical indices, fresh
>> statistics, similar buffer pool allocation and no special tuning.
Uh, NO! If the sub-query is not correlated, then Informix executes it once
only. If the sub-query IS correlated, then Informix unwinds the sub-query
performing a simple join instead (this is called sub-query flattening) again
only performing the sub-query once.
Art S. Kagel
> The IN in Informix must work VERY differently from Oracle. In Oracle your
> reads are always isolated from otheres writes. It's a basic principle in
> Oracle. This allows it to execute the "IN" only once and use their values in
> each cycle of the outside table.
>
> Informix works very differently. It must execute the subquery for every
> cycle of the outside table because it isn't isolated from others writes to
> the inner table...
>
> It's a concept completely different which has consequences. Not
> understanding this and using this as excuse (as the original post mentioned)
> is not IMHO correct.
>
> Regards.