Re: performance using inequality query

Check if your bind variables are of date datatype. If not, do explicit conversion on them. (to_date).

In article <95rpb0$gkv$1_at_nnrp1.deja.com>,   gastineau_at_my-deja.com wrote:
> 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/
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 08 2001 - 15:42:35 CST