Re: Identify Optimizer issues

mohitanchlia@gmail.com wrote: > On Jan 4, 2:11 am, TBP <TheBigPot...@NotHere.Co.Uk> wrote: >> Jonathan Leffler wrote: >>> mohitanch...@gmail.com wrote: >>>> Version: IDS 10 >>>> Recently I saw some inconsistencies in optimizer taking incorrect >>>> index path. This Bug has been reported. Now I am attempting to do the >>>> following: >>>> 1. Write perl script to get all the "Select" from the code. This seems >>>> to be easy. >>>> 2. Execute the select statement with explain on. >>>> 3. Parse the results in sqexplain file to determine if it took correct >>>> index path. Basically, for large tables it should take index path if >>>> head of the index is part of the where clause. Is there a good way of >>>> doing this ? Has anybody done this before ? I am trying to proactively >>>> identify those queries that has problems, so that we can use Index >>>> directives, if required, while bug is being fixed. >>> I've seen Art's august discussions, but you cycled around to reiterating >>> your question, so I'll start answering the original question again... >> <snip> >> >>> So, it 'can' be done: IDS does it all the time. It is non-trivial. >> I have pondered about this, and ... >> >> What are actually trying to achieve? >> >> To write an "optimiser verifier" is quite a big task. And is that really >> what you want to do? What percentage of your queries are giving you >> problems? If you are trying to find queries which "match" the defect / >> bug you have found and reported, then that may be achievable in a >> straight forward logic way; but give us a clue - what was the defect, >> and what specific version of 10? How does it manifest itself, what are >> its characteristics? > > All I want to know is if a column is a head of the index "IX_A" and is > used in where clause then optimizer is choosing index "IX_A" as the > INDEX_PATH and not some other index. For eg: if table A has column > a,b,c and column a and column b has an index IX_A and column b and c > has an index IX_B then select * from A where a =1 and b =2 is choosing > IX_A. Be careful - there could be more indexes to worry about... In your three column example, indexes could include (contrived, but..): CREATE INDEX IX_A ON TableA(a, b); CREATE INDEX IX_B ON TableA(b, c); CREATE INDEX IX_C ON TableA(b, a, c); With query: SELECT * FROM TableA WHERE a = 1 AND b = 2; Then the best query plan will be an index-only scan on IX_C. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/ publictimestamp.org/ptb/PTB-2205 tiger2 2008-01-06 03:00:06 A0539A0B5D9656C2AA1CF58CF58EF717BBE68CED1F9F06E7