Re: Index usage question

Hi, Daniel:

Thanks for responding.

I have brought the stats up to date as of this morning (It was a couple of days old).

I seem to get two different plan results depending on whether I use the SQL Scratchpad or SQL*Plus. SQL*Plus claims it is using the correct index while the scrathpad claims a full scan (I'm not sure which is correct, though I suspect the SQL*Plus may be as I am drawing the information from the plan_table directly) Have you seen this type of behaviour before?

Cheers,

Mark.

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EF731EE.B79E129F_at_exxesolutions.com...
> 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.
>
> Are you keeping statistics current for the CBO with DBMS_STATS?
> What is the EXPLAIN PLAN with three?
> Have you tried hints?
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
>
Received on Mon Jun 23 2003 - 14:31:53 CDT