Re: performance using inequality query

Nicely put.

This type of query is always a pain unless you can put a tight boundary on the differences between start and end date (and in this case the original author said it could be between minutes and months).

There is one interesting possibility that I haven't tried - no doubt data dependent to a great degree:

Single column compressed index on start_date Single column compressed index on end_date

Set _b_tree_bitmap_plans = true

This gives Oracle the option for range scanning the two b-tree indexes, converting the resulting rowid ranges to bitmaps, ANDing the bitmaps and converting back to rowids for fetches. It __might__ be reasonably quick, as it combines small physical indexes with high precision table access.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Dave Wotton wrote in message ...



>>SELECT *

>>FROM METER_READ

>>WHERE

>> START_DATE  < :end_date AND

>>END_DATE  > :start_date;





>

>Even using an index, this query will still be inefficient. Suppose an index

>on START_DATE is used. The query will have to read every row with

 START_DATE


>less than the bind variable and then test the value of END_DATE for each

 one.


>A similar problem occurs if an index on END_DATE is used.

>