Re: Use of index query with "or"

On 5/31/06, Habichtsberg, Reinhard <RHabichtsberg@arz-emmendingen.de> wrote: > IDS 9.40FC4W2 on Solaris 8/9 > > We have a bad performing query with some "or"-relations. > > Example: > select some rows from table1 > where r1 = "value1" and > (r2 = "value2" or r2 is null) and > (r3 = "value3" or r3 is null) and > (r4 = "value4" or r4 is null) and > (r5 = "value5" or r5 is null) and > ... > > table1 has an index1: r1, r2, r3, ... > and an index2: r1, r2, rx > > The optimizer chooses index2 and as lower index filter only row r1. > > We expected the optimizer to choose index1 and lower index filter r1, r2, > r3. The performance would be much better. The trouble is that the optimizer can't tell very much about the structure of the possibly null columns. If, for example, r2 is null, those rows will appear before the rows where r2 = "value2". Of itself, that is not insurmountable, but the optimizer cannot tell whether r3 can be null independently of r2 - so it has to assume that it could be. If, in fact, r2 is null ==> r3 is null and r3 is null ==> r4 is null and r4 is null ==> r5 is null, then you might be able to write the query more rigorously. But the optimizer has a tough job to improve on the selectivity other than scanning the index over the range where r1 = "value1". > We have not the option to use a union-select or to use for example "r2 in > ("value2", null)" nor to set optimization or optimzer directives from the > program in question. Actually we have no chance to alter the (UNIFACE-) > program. > > We would be very glad if you could give us some recommendations how we can > solve this problem. If you were on IDS 10.00, you could consider using the SAVE EXTERNAL DIRECTIVES feature (carefully), and if there is a directive that improves the performance of this query - and the values are either fixed and always the same or (more plausibly) variable but always passed by parameter (place-holders in the SQL), then you might do some good. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/