simple query not using index

Hi,

I'm trying to make this simple query user the index I created, but it just doesn't want to :)

I have a table 't' with a column 'user_id' that is a foreign key. The query that I want to use the index is:

  SELECT * FROM t WHERE user_id=1

Using 'set autotrace on' I can see that the table 't' is being fully scanned and that the index I created is not being used:

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=54 Card=387 Bytes=21
          672)

   1    0   SORT (ORDER BY) (Cost=54 Card=387 Bytes=21672)
   2    1     TABLE ACCESS (FULL) OF 'T(Cost=47 Card=387 Bytes=21672)

I created my index like this:

  CREATE INDEX my_i ON t(user_id);

I also did:

  ANALYZE TABLE t COMPUTE STATISTICS

Does anyone know why the above simple query is not using the index I created?

Thank you,

Otis

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 13 2000 - 00:00:00 CDT