Re: simple sql using between startnumber and endnumber not performing

=?iso-8859-1?q?Benjamin_S=F8lberg?= (benjamin.soelberg_at_gmail.com) wrote:

: Create an index on substr(startnumber, 1, 4), substr(endnumber, 1, 4),
: startnumber, endnumber

: Then do a SQL like this :
: SELECT *
: FROM numbers
: WHERE '3131' BETWEEN SUBSTR (startnumber, 1, 4) AND SUBSTR
: (endnumber, 1, 4)
: AND '31313131' BETWEEN startnumber AND endnumber;

: Is this correct ?

I can't say off hand. I think if you use a functional index then the syntax used within the where must be identical to the syntax used in the functional index.

The "traditional" way would be to add columns that have derived values and include those columns in the query

table X

	startnumber NUMBER
	endnumber   NUMBER
	start_helper NUMBER (always has part of startnumnber)
	end_helper   NUMBER (always has part of endnumnber)

index on all four columns

SELECT *
   FROM X
  WHERE '3131' BETWEEN start_helper and end_helper     AND '31313131' BETWEEN startnumber AND endnumber;

More than this I cannot say, at this stage I would be experimenting to check my understanding. Received on Wed Jun 27 2007 - 18:49:03 CDT