Mark,
Don't rely on the implicit string to date conversion. Explicitly convert
it.
eg
AND reportdatentime >= '25-Mar-2003'
AND reportdatentime < '21-Jun-2003'
should be:
AND reportdatentime >= to_date('25-Mar-2003','dd-mmm-yyyy')
AND reportdatentime < to_date('21-Jun-2003','dd-mmm-yyyy')
If someone changes the nls date format default on the client the query won't
work. This method is much safer.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"MThomas" <markt_at_wrx-ca.com> wrote in message
news:5CIJa.259$Fy1.10062_at_localhost...
> 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 - 22:23:06 CDT
