7.30UC2 - Subquery uses Autodindex instead of Index

Hello, I have this problem after upgrading a server from 7.20 to 7.30UC2. The optimizer appears to process subquerys diffrently when the in clause is used. Rather than using the index on the table used in the subquery it uses an autoindex instead according to the set explain outptut. I understand this is a known problem with IDS 7.3 and there is an informix workaround which involves changing/adding indexes to the subquery table. Does anyone else know an alternative solution to this problem which does not rely on adding/modfiying indexes??? -- Richard Peterson richard@richard-peterson.demon.co.uk

Richard Peterson wrote: > > Hello, > > I have this problem after upgrading a server from 7.20 to 7.30UC2. The > optimizer appears to process subquerys diffrently when the in clause is > used. Rather than using the index on the table used in the subquery it uses > an autoindex instead according to the set explain outptut. > > I understand this is a known problem with IDS 7.3 and there is an informix > workaround which involves changing/adding indexes to the subquery table. > > Does anyone else know an alternative solution to this problem which does not > rely on adding/modfiying indexes??? This is not REALLY a 7.30 problem, rather a new feature which is breaking your database design. In 7.3x the optimizer recognizes correlated sub queries and flattens them into joins which are normally MUCH faster. The problem you describe is that the index needed to perform that join efficiently is not present on one or the other table and so the optimizer performs either a dynamic hash join or creates a dynamic index, as it did for you, which takes time and adds to the queries runtime. The recommended solution, as you mention in passing, is to manually create the missing index and make your database design better support the flattened query. NB that if the engine is doing this to your queries then you should rewrite the query into a join yourself (ALL correlated sub queries can be rewritten as simple joins). There is an alternative or two you can use until you can get around to improving your schema with the new indexes. There is a new ONCONFIG parameter, NOSUBQF, setting it to '1' will disable the Sub Query Flattening code and make 7.3x behave like 7.2x in this instance. I believe there is also an optimizer directive you can patch into your SQL itself to disable flattening for that query alone. However, note well that the flattened query will ALWAYS outperform the sub query version, and that a manually flattened sub query will be slightly faster still (because it by-passes that optimizer function), once the correct indexes are in place. If your code is filled with correlated sub queries your problem is that you, or the programmers who wrote the SQL anyway, are stuck thinking like a programmer. Always remember that SQL is a USER level language NOT a programming language. If you phrase the query in English like a programmer you will ALWAYS come up with a correlated sub query (I've seen it hundreds of times) but if you phrase the query in English like a USER would tell it to you the you will ALWAYS come up with a join instead. TRUTH! This is Kagel's Second Law of SQL. How do you do this? Take off your programmer head when writing SQL and put on your user head. Until that becomes second nature - literally saying to yourself "I'm taking off my programmer head and putting on my user head now.", as silly as it sounds, will help you focus on the correct paradigm for writing efficient SQL. Art S. Kagel

Art S. Kagel wrote: . . . lotsa good things snipped . . . > If your code is filled with correlated sub queries your problem is > that you, or the programmers who wrote the SQL anyway, are stuck > thinking like a programmer. Always remember that SQL is a USER level > language NOT a programming language. If you phrase the query in > English like a programmer you will ALWAYS come up with a correlated sub > query (I've seen it hundreds of times) but if you phrase the query in > English like a USER would tell it to you the you will ALWAYS come up > with a join instead. TRUTH! This is Kagel's Second Law of SQL. > OK, I'll bite . . . what's Kagel's First Law of SQL? John Carlson Informix DBA WHSmith USA