Re: simple query not using index

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: 2000/04/16
Message-ID: <8db4ku$fle$1@nnrp1.deja.com>#1/1

In article <lJ6K4.780$q2.27105975_at_news.wctc.net>,   "Stanton W. Schmidt" <sschmidt_at_wctc.net> wrote:
> 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.

I don't think that is the case here:

SQL> select count(user_id) from nm_messages;

COUNT(USER_ID)

---

          4875

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

COUNT(USER_ID)

---

           921

20% of rows have user_id 23

The query that I want to use index is:

select email from nm_messages where user_id=23 order by receive_date;

No go, full table scan :(

Otis

> 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 Sun Apr 16 2000 - 00:00:00 CDT