Re: simple sql using between startnumber and endnumber not performing

=?iso-8859-1?q?Benjamin_S=F8lberg?= (benjamin.soelberg_at_gmail.com) wrote:
: 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.

If they are actually strings then using the first digit would be the same general idea.

: One question:
: If there are more than 9999 between fromnumber and tonumber will this
: still work ?

Are you saying that there is a correspondence between the two numbers in each row? If so then some function of the two numbers should perhaps be indexed instead of the two numbers independently.

: Say i select from 20000000 to 30000000.
: I belive not and in this case i should select using only startnumber
: and endnumber right ?

No, the underlying issue is the same.

The point of the index can be determined by trying to sort a list of pairs of numbers. The following is sorted on the first number.

	1	10	
	2	5
	3	8
	4	1
	5	1
	6	3

notice that sorting on the first number helps you find the first number efficiently, but it will not help in finding the second number. On average, the first number is in the middle of the list, but then you have to scan through all the rows _starting_ at that point in the list to find the second number. (i.e. on average you still half to scan through half the rows).

What happens if you sort the above on both numbers? - well actually the list is already sorted on both numbers! It doesn't help because the first numbers are all unique so each first number corresponds to a single second number.

The point of the suggested index is to partition the first numbers into lumps. You can efficiently find which lumps probably have the first number because that is sorted, but within each lump there is a sorted set of second numbers to help you efficiently find the second numbers in each lump. Each level of partitition should, on average, half the rows to be examined.

The original numbers must be examined at some point, so you put them are in the index so that only the index needs to be read, not the table.

HOWEVER, the indexing adds over head, so at some point the technique starts to become less efficient, therefore experimentation is required to see if this will even help in your situation.

Also, if the numbers already have many repetitions then the above will do nothing except add over head, so as I said, experimentation and understanding is required to see if this will even help in your situation. Received on Wed Jun 27 2007 - 16:41:53 CDT