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.