Finding matching ranges

I have a table with to columns x and y that describe range boundaries. I want to find all rows, whose ranges cover a given value, e.g.

select * from table
where givenValue betwen x and y
;

Now the question is: how can I do this fast ? Typically oracle would use an index range scan on either x or y and compare ALL the resulting rows against givenValue (this could be all the rows in the table).

I was wondering If I could use a redundant column "g" to speed things up. Ideally I would transform the problem

        x < given Value < y

to something like

        f1(givenValue) < g(x,y) < f2(givenValue).

This would reduce the problem to a simple range scan on the redundant column g(x,y). But I have no idea what functions f1, f2 and g to use (apart from the obvious trivial solution f1=0, g=1, f2=2, which would be logically correct, but would provide absolutely no selectivity). Received on Sat Jul 21 2001 - 18:30:01 CDT