Re: Index usage question

Hi, Brian:

Thanks for the quick response.

Yes we are using the CBO, and I just updated the statistics for the table (using the DBMS_STATS package as you suggested). Unfortunately the optimizer still seems to think the full table scan is faster (not sure why, in my mind this would be extordinarily slow in comparison).

Is there a way to force it to use the index? I have been looking at hints, but so far I haven't quite figured out how to use them correctly.

Thanks,

Mark.

> I'm assuming that you are using CBO. The CBO is determining that index
> lookups with the third value is more costly than a full table scan. The
> first thing to do is to make sure that you have up to date statistics.
> And use the DBMS_STATS package, not the ANALYZE command.
>
> HTH,Brian
>
> MThomas wrote:
> >
> > Good morning:
> >
> > I am having some difficulty understanding the behaviour of Oracle 9i
> > (9.2.0.2 on Windows 2000 Server) during a retrival.
> >
> > The query is in the form:
> > SELECT * FROM iohistory
> > WHERE ioid IN (63515, 63516)
> > AND reportdatentime >= '25-Mar-2003'
> > AND reportdatentime < '21-Jun-2003'
> >
> > When the query is executed for one or two ioid values , the proper index
is
> > used. However when a third point is addes to the retrieval a full table
> > scan is initiated (this is a problem as the table currently contains
> > ~60,000,000 records). Ideally it would always (or nearly always) use
the
> > index.
> >
> > The index is unique on the ioid and reportdatentime columns of the
table.
> >
> > Would anyone have an idea how I may correct this behaviour?
> >
> > Thanks for your help.
> >
> > Mark.
>
> --
> ===================================================================
>
> Brian Peasland
> oracle_dba_at_remove_spam.peasland.com
>
> Remove the "remove_spam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good. Now pick two out of
> the three"
Received on Mon Jun 23 2003 - 11:48:06 CDT