Frank van Bortel (frank.van.bortel@gmail.com) wrote:
: -----BEGIN PGP SIGNED MESSAGE-----
: Hash: SHA1

: Malcolm Dew-Jones wrote:
: >
: > table X
: > startnumber NUMBER
: [snip]
: > index on all four columns
: >
: > SELECT *
: > FROM X
: > WHERE '3131' BETWEEN start_helper and end_helper

: Not using index - implicit conversion - BAD!

Ok, that should be a number, I simply cut/pasted the original text of the
question above that.

The issue of trying get a useful index out of two columns of numbers will
still be the same.

(And yes, I understand that this may not be an issue here, but I've
pointed out the user needs to experiment to be sure, and the double column
number index is a thing I have had to deal with in the past, so I know
that at least some of the time, in the past, this was an issue.)

On 27 Jun., 22:19, Benjamin Sølberg <benjamin.soelb...@gmail.com>
wrote:
> Yes the numbers are infact Strings.. actually varchar2(8)
> I would very much like to change it but its an old and huge system of
> which I only have partly control.

So it's all numbers in those strings? Then I'd make an index based on
the numbers, because comparing numbers is much faster than comparing
strings.

create index num_idx on my_table ( to_number(startnumber),
to_number(endnumber) )