Re: opinions on this SQL statement

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.