Strange index/optimizer behaviour

Ok,

Maybe I'm just overlooking something, but this on i just can't solve:

I have the following table:
year number(4)
period number(2)
...
some datacolumns
...
non-unique index on (year, period)
no primary key index (maybe that has something to do with it, i'll try)

When i submit the following query:
select *
from table
where periode <= 4
=>I don't get any rows.

When i submit the following query:
select *
from table
where periode = 4
=>I do get the records from period 4

When i submit the next query:
select *
from table
where periode in (1,2,3,4)
=>I get the result i want.

So there is a workaround, but i want to know what is happening here. By the way: when i drop the index, all queries produce the expected result (albeit a bit slower).

Has anybody had similar experiences or does anyone see what i'm doing wrong? It concerns Oracle 8.0.4 enterprise edition on Windows NT4

TIA,
Tony van der Prijt Received on Sun Nov 22 1998 - 14:04:21 CST