Re: simple query not using index

You did not say how many records are in the table and how many unique USER_ID's there are. The optimizer uses this information to help determine whether using the index is faster than doing a tablespace scan. If the optimizer determines that it will retrieve a certain percentage of the records (I can't remember the percentage, but it is surprisingly low) then it will perform a tablespace scan instead.

Otis Gospodnetic <otis_at_my-deja.com> wrote in message news:8d7p37$uvb$1_at_nnrp1.deja.com...
> In article <38F729BE.C4E1A4B_at_edcmail.cr.usgs.gov>,
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote:
> > What data type is USER_ID? If USER_ID is varchar or char, then you are
> > unintentionally suppressing the index in your WHERE clause.
>
> It's an integer, or actually NUMBER (Oracle):
>
> SQL> describe nm_messages;
> Name Null? Type
> ----------------------------------------- -------- --------------------
> USER_ID NOT NULL NUMBER(38)
> ...
>
> Not varchar....it's a number, it really could use the index! :)
>
> Thanks,
>
> Otis
>
>
> > 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?
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Apr 15 2000 - 00:00:00 CDT