Oracle - Index seems not to work. Still slow

Oracle Database: I have a table with 400000 rows. I create an index for field1. The following query is still very slow (700ms):

select field1, field2
from table
where 
field1 = '0903400110106156' or
field1 = '0903400110106160' or
field1 = '0903400110106190' or
field1 = '0903400110106471' or
field1 = '0903400110106480' or
field1 = '0903400110106494' or
field1 = '0903500110100001' or
field1 = '0903500110100012' or
field1 = '0903500110100021' or
field1 = '0903500110100031' or
field1 = '0903500110100039' or
field1 = '0903500110100047' or
field1 = '0903500110100050'

I drop the index and I still get 700ms. I create the index again and I still get 700ms. What is wrong???

The create index statement:

CREATE INDEX myindex
ON table (field1)

EDIT: Explain Plan

PLAN_TABLE_OUTPUT

-----------------------------------------------------------------------
| Id  | Operation            |  Name          | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |     4 |    60 |     2 |
|*  1 |  TABLE ACCESS FULL   | table          |     4 |    60 |     2 |
-----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("table"."field1"='0901690339400674')

Note: cpu costing is off

EDIT2: OK, I did a table analysis for 2 tables. table1 has the speed problem. table2 is a lot faster (10msec instead of 700msec) although its size is similar to table1. Strange speed problem!!! Please help...

chk     Owner   Name    Partition   Subpartition    Tablespace  NumRows  Blocks  EmptyBlocks    AvgSpace    ChainCnt    AvgRowLen   AvgSpaceFLBlocks    NumFLBlocks UserStats   GlobalStats LastAnalyzed     SampleSize Monitoring  Status                            PartType      PartInfo     IsExternal
TRUE    user    table1                             tablespace  4846开发者_开发知识库27   6858    182            878         777         103                                         NO          NO          15/3/2011 18:34  125977     NO          Normal, Successful Completion                                FALSE
TRUE    user    table2                             tablespace  366159   6480    176            786         16565       130                                         NO          NO          15/3/2011 18:34  89657      NO          Normal, Successful Completion                                FALSE

The question is a bit old meanwhile but since I had the same problem right now and no answer worked for me here is the correct answer which is missing in my opinion. After creating an index use following statement to tell Oracle that there is a new index and it has to use it whenever possible.

ANALYZE TABLE <tablename> COMPUTE STATISTICS;

As an alternative you can tell Oracle to compute statistics just in the moment you create a new index by:

CREATE INDEX myindex ON table (field1) COMPUTE STATISTICS;

try

where
field1 in ('0903500110100050', '0903500110100050', .. )

It doesn't matter whether or not you have an index on field1. The database has to read the entire table before it can satisfy an OR condition (or an IN predicate) on the WHERE clause.

It would be faster to put an index on field1 and request field2 for one field1 at a time (WHERE field1 = :value) in a loop.

Try an index on (field1, field2)

CREATE INDEX myindex
ON table (field1, field2)

OK, Solved.

Yesterday before I leave work to go home I did a last try and my query was still slow (no index use).

Today in the morning before I do anything else I did another try and suddenly the index works and I get only 10 msec.

If you have any explanation please post it.

I am adding the last explain plan:

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------
| Id  | Operation                   |  Name                | Rows  | Bytes | Cost  |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    23 |     4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| table                |     1 |    23 |     4 |
|*  2 |   INDEX RANGE SCAN          | myIndex              |     1 |       |     3 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("table"."field1"='0901690339400674')

Note: cpu costing is off