Use of index query with "or"
Posted in 2006
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. 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. TIA, Reinhard.