Re: simple query not using index

The optimizer can only use that information if you have a histogram built on the column otherwise it doesn't know what your spread of data is. I can only think that the optimizer is seeing low cardinality in that index and deciding that it's not worth the extra reads. How many rows do you have in that table? You could use a hint to point the optimizer towards that index but it may ignore that too !

regards

Mike Hately

Stanton W. Schmidt <sschmidt_at_wctc.net> wrote in message news:lJ6K4.780$q2.27105975_at_news.wctc.net...
> 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 Mon Apr 17 2000 - 00:00:00 CDT