Re: simple query not using index

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: 2000/04/17
Message-ID: <8df88d$op5$1@nnrp1.deja.com>#1/1

In article <8ddme6$1n6$1_at_lure.pipex.net>,   "Mike Hately" <mike.hately_at_virgin.net> wrote:
> 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?

Here are the counts:

SQL> select count(user_id) from nm_messages;

COUNT(MSG_UID)

---

          4580

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

COUNT(MSG_UID)

---

           937

> You could use a hint to point the optimizer towards that index but it
 may
> ignore that too !

How can I do that? I can't find the syntax for that in 3 Oracle books that I have here.... :(

Thanks,

Otis

> 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.
> >
> >

>
>

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