Re: select value within an interval

G'day.
Use of this sort of range data depends on the constraints that low_range <= high_range and that the ranges are non-overlapping. The row you want has the largest low_range less than or equal to the product_id.
To find this,
SELECT /*+ FIRST_ROWS_1 INDEX_DESC(product_types) */ * FROM product_types
WHERE low_range <= :product_id
ORDER BY low_range DESC
then fetch only the first row.
If you have trouble with the index_desc, try indexing high_range and fetching the first row from
SELECT /*+ FIRST_ROWS_1 */ *
FROM product_types
WHERE high_range >= :product_id
ORDER BY high_range

Do not use rownum <= 1 to get just the first row - that will return the wrong result if the index become unusable.

In principle, it is possible to use the index to quickly answer the query:
SELECT max(low_range) FROM product_types WHERE low_range <= :product_id but I haven't got a database available to test whether the Oracle optimiser can do this.

Regards,

    David Penington Received on Tue Apr 18 2006 - 20:13:25 CDT