Re: simple query not using index

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 Fri Apr 14 2000 - 00:00:00 CDT