Re: simple query not using index

What data type is USER_ID? If USER_ID is varchar or char, then you are unintentionally suppressing the index in your WHERE clause.

HTH,
Brian

Otis Gospodnetic wrote:
>
> 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.
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================