Re: Index usage question

Good morning.

Thank you Jim, Daniel and Brian for you help sorting myself out.

I have been able to get Oracle to rely on the index I require. A secondary index on the table also seemed to be interferring with Oracle's selection of the best way to select. After removing the extraneous index and resetting the statistics, the performance seems to have come back into line (we now seem to be bound through the network, but that's another project.

Have a good day all,

Mark.

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:uuPJa.4061$Bg.1831_at_rwcrnsc54...
> 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 Tue Jun 24 2003 - 09:52:02 CDT