Re: simple sql using between startnumber and endnumber not performing

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 26 Jun 2007 19:45:04 -0800
Message-ID: <4681cf30$1@news.victoria.tc.ca>

=?iso-8859-1?q?Benjamin_S=F8lberg?= (benjamin.soelberg_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 Received on Tue Jun 26 2007 - 22:45:04 CDT