performance using inequality query

I have a table of the following structure

METER_READ
  METER_ID
  START_DATE
  END_DATE
  READING It contains one entry per time period (START_DATE->END_DATE). Usually this time period is an hour, but can be seconds, minutes, hours, days, weeks, months, years. In a query, I would like to return all records that overlap the a specified time interval.

SELECT *
FROM METER_READ
WHERE
:end_date > START_DATE AND
:start_date < END_DATE;

The table has a 2-column index on START_DATE,END_DATE. The table has been loaded with 1 million records spread out across 6 months and analyzed. The explain plan chooses a full table scan for the above query unless an index hint is added. The query for 1 days records takes 25-30 seconds using a full table scan and <3 seconds with use of the index.

If possible, I'd prefer to avoid using the index hint. The real queries used in the application involve many more tables, and depending on the parameters, the index is not always the best strategy for the query. There is also quite a bit of dynamic SQL.

Thanks,
Brian Gastineau

Sent via Deja.com
http://www.deja.com/ Received on Wed Feb 07 2001 - 09:24:25 CST