Oracle not using indexes on char/varchar columns

Hello,

We've migrated to Oracle 9.2.0.1.0 (Solaris, 64bit) and are encountering problem with indexes. Oracle won't use indexes on char/varchar columns, until any (even unrelated to the query or the table referenced in query) 'explain plan' is run in that session. The first 'explain plan' of the query says that 'FULL SCAN' would be used, the same 'explain plan' ran for the second time gives correct information (INDEX UNIQUE SCAN).

What to do? Workaround would be to call 'EXPLAIN PLAN FOR SELECT * FROM DUAL' at the beginning of every session, but i don't like that :)

Thanks,

Kamil

Example:

CREATE TABLE TEST
(

   COL1 varchar2(4) primary key
)

...
insert data
...

select * from TEST where COL1=1;

• wait several minutes, select is not using index

explain plan for select * from TEST where COL1=1

• says 'FULL SCAN'

explain plan for select * from TEST where COL1=1

• says 'INDEX UNIQUE SCAN'

select * from TEST where COL1=1;

• done immediately

  ---