Re: Index Usage Question

From: Joseph Weinstein <joe.remove_this_at_bea.com.remove_this>
Date: Mon, 23 Jun 2003 10:00:03 -0700
Message-ID: <3EF73213.58B80CF5@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 - 12:00:03 CDT