Re: performance using inequality query

hi,

have you tried:

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

AFAIK Indexes can only be used for columns standing on the left side of an expression.

hth
Stephan

gastineau_at_my-deja.com schrieb:

> 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:50:56 CST