Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> 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! ========================================Received on Fri Apr 14 2000 - 00:00:00 CDT
![]() |
![]() |