Re: Temp dbspace problem

drumzspace wrote: > > In article <37C2F4C3.BD9361F5@bloomberg.net>, > kagel@bloomberg.net wrote: > > It may be sub-query flattening, a new feature of the 7.31 optimizer > that > > tries to unwind corellated sub-queries into ordinary joins at runtime > so > > that the query can be executed, in theory, more quickly. However, if > you > > do not have the indexes to support the joins, and the heavy temp > space use > > would suggest that you do not, then these queries will use even more > temp > > space and take longer to run. Have you run the query on 7.31 under > SET > > EXPLAIN ON? The output will show if there has been any attempt to > flatten > > the query and what indexes if any are being used. If this is the > case > > add the missing indexes so that the join can take place using indexes > and > > not using temp space. Another alternative, not as nice but quick and > > dirty would be to set the NOSUBQF environment variable, or add the > > equivalent optimizer directive, to disable sub-query flattening. > > > > Art S. Kagel > > > > After I triple checked the sqexplain.out file against the indexes on > one of the tables I found that the whole set of indexes was bad. One > of those so-close-you-don't-even-see-it things. Since I had never seen > this extreme behavior with 7.3UC5's subquery flattening before I hadn't That's because 7.30 did not do sub-query flattening it is a 7.31 feature. Art S. Kagel > checked the indexes thoroughly enough; now I know. > > Incidentally, when I called Informix about this, the guy from support > had me drop the temp db spaces and recreate them (with a few onconfig > changes and engine bounces in between). He said that there were a > couple of bugs that fit the description...so watch out(?) > > A million thanks, Mr. Kagel. > > .ben. > > -- > to reply: drumzspace (AT) yahoo (DOT) com > > Soul Pagoda: http://www.mp3.com/soulpagoda > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.