Re: Index Usage Question

your range scan is forcing the full table scan.

     AND reportdatentime >= '25-Mar-2003'
       AND reportdatentime < '21-Jun-2003'

Oracle can only perform an index range scan(greater than, etc...) if the columns involved are flagged not null.

you can sometimes force an index scan if you add

reportdatentime is not null
and reportdatentime is not null

this usually the works. The reason is that when doing a range scan oracle has to include nulls in its search. However, nulls are never indexed. if adding not null does not work you have a couple of options.

turn the following into a view.

SELECT * FROM iohistory
  WHERE ioid IN (63515, 63516)

this will limit your result set. then do a select on the view with your range scan. You try turning that into an inline view. you might have to use a hint to keep oracle from reverting back to the original plan.

Option 2:
range partition your 60m row table

"Joseph Weinstein" <joe.remove_this_at_bea.com.remove_this> wrote in message news:3EF73213.58B80CF5_at_bea.com.remove_this...
>
>
> 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.
>
> You might give UNIONs a try:
>
> SELECT * FROM iohistory
> WHERE ioid = 63515
> AND reportdatentime >= '25-Mar-2003'
> AND reportdatentime < '21-Jun-2003'
> UNION
> SELECT * FROM iohistory
> WHERE ioid = 63516
> AND reportdatentime >= '25-Mar-2003'
> AND reportdatentime < '21-Jun-2003'
> UNION
> SELECT * FROM iohistory
> WHERE ioid = 63517
> AND reportdatentime >= '25-Mar-2003'
> AND reportdatentime < '21-Jun-2003'
> ... etc.
> This might be quicker because each query is
> independent, and is perfect for the index.
>
> lemme know...
> Joe Weinstein at BEA
>
> >
> >
> > 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.
>
Received on Mon Jun 23 2003 - 19:51:01 CDT