Re: simple sql using between startnumber and endnumber not performing

On 27 Jun., 05:45, y..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> =?iso-8859-1?q?Benjamin_S=F8lberg?= (benjamin.soelb..._at_gmail.com) wrote:
>
> : 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 ?
>
> create an index on four columns, where the first two columns contain
> truncations of the two numbers.
>
> indexed columns would be
> ( integer portion of startnumber/1000
> integer portion of endnumber/1000
> startnumber
> endnumber
> )
>
> the 1000 is just an example, some other number is probably better
>
> you might have to query on the truncated number as well as the real
> number.
>
> WHERE integer portion of 12345678/1000 between
> integer portion of startnumber/1000 and
> integer portion of endnumber/1000
> AND 12345678 between startnumber and endnumber
>
> $0.10

Hello Malcolm,

Thank you for your fast reply.
I was thinking about something like this, not an index based but just the first digit as each number are all 8 digits. But your idea seems like a better way.

One question:
If there are more than 9999 between fromnumber and tonumber will this still work ?
Say i select from 20000000 to 30000000.
I belive not and in this case i should select using only startnumber and endnumber right ?

Regards
Benjamin Received on Wed Jun 27 2007 - 07:26:16 CDT