Index usage question

From: MThomas <markt_at_wrx-ca.com>
Date: Mon, 23 Jun 2003 09:54:33 -0600
Message-ID: <kqFJa.220$Fy1.9360@localhost>

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. Received on Mon Jun 23 2003 - 10:54:33 CDT