simple sql using between startnumber and endnumber not performing

Hi all

I have a simple sql which I can't make perform.

In its simplest form it has two cols, a startnumber and an endnumber. None of the rows overlap with another.

I am using an sql which looks like this:

select startnumber, endnumber from table where '12345678' between startnumber and endnumber

I have also tried
select startnumber, endnumber from table where '12345678' >= startnumber and '12345678' <= endnumber

The latter sometimes performes a little better.

The table has about 6 mill rows.
I have tried with indexes on both rows, both with desc and asc as FBI in any combination that i can think of and also hinted the sql.

The explain plan shows that it will use the index but the query takes around 6 to 9 seconds which is way to much in respect of the needs.

Does any one of you have any suggestions ?

Regards
Benjamin Received on Tue Jun 26 2007 - 20:02:48 CDT