Re: simple query not using index

In article <20000414.5234200_at_noname.nodomain.nowhere>,   Jerry Gitomer <jgitomer_at_erols.com> wrote:
> What percentage of the rows in your table have a user_id of 1?
 If=20
> more than 25% the index won't be used. If between 10% and 25% the=20
> index won't be used if the optimizer "thinks" it would be faster to
 do=20
> a full table scan.

I see.
But that is not the case here:

SQL> select count(user_id) from nm_messages;

COUNT(USER_ID)

          4753

SQL> select count(user_id) from nm_messages where user_id=21;

COUNT(USER_ID)

           128

SQL> select user_id from nm_messages where user_id=21;

....

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=47 Card=387 Bytes=18
          189)

   1    0   TABLE ACCESS (FULL) OF 'NM_MESSAGES' (Cost=47 Card=387 Byt
          es=18189)

I just can't figure it out...it does a full table scan instead of using index on nm_messages(user_id) that I made :(

Thanks,

Otis

> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> On 4/13/00, 6:16:57 PM, Otis Gospodnetic <otis_at_my-deja.com> wrote=20
> regarding 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=3D1
 

> > 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=3DCHOOSE (Cost=3D54 Card=3D387
 By=
 tes=3D21
> > 672)
 

> > 1 0 SORT (ORDER BY) (Cost=3D54 Card=3D387 Bytes=3D21672)
> > 2 1 TABLE ACCESS (FULL) OF 'T(Cost=3D47 Card=3D387
 Bytes=3D2=
> 1672)
>
> > 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.
>
>

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