We are using secondary indexes and see the following issue. Consider main table ACT with primary key (K1, K2, K3) and column M1 and secondary index of form -
CREATE INDEX IF NOT EXISTS act_idx ON ACT(K1, K3, K2);
Then query of the following form gives incorrect results -
select /*+ INDEX(ACT act_idx) */ K1, K2, K3 from ACT where K1 = ? and K3 = ? and M1 = ? order by K1,K3 limit 10;
What seems to happen is filter on M1 is applied after the limit is applied rather than limit being applied after all the filters have applied. Is this an expected issue?
I apologize if this question has been asked before.