Re: simple sql using between startnumber and endnumber not performing

On 27 Jun., 20:50, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Benjamin Sølberg wrote:
> > I am using an sql which looks like this:
>
> > select startnumber, endnumber from table where '12345678' between
> > startnumber and endnumber
>
> What is it - numbers or strings?!? If it's a number, loose
> the quotes. (If it's a string, I'd make you rename the
> columns in the design!)
>
> > Does any one of you have any suggestions ?
>
> Yes
>
> - --
> Regards,
> Frank van Bortel
>
> Top-posting is one way to shut me up...
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (MingW32)
>
> iD8DBQFGgrFiLw8L4IAs830RAtkmAKCSRdO2Ab4yEwWHIN4FWLM4gTcUmgCfWlzb
> MU7zlqalHvZFFTStEVDV42o=
> =ZFK7
> -----END PGP SIGNATURE-----
Hi Frank
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.

Anyway each start and endnumber is a telephonenumber (just 8 digits where I live)
Normal start and endnumber is the same (in 80% of the rows) but often its a series of numbers.
I tried the FBI part with substr(startnumber, 1, 4) and the same on endnumber as well as using start and endnumber in the query as suggested.
I even hinted the SQL and the index is used but is infact 50% slower.

So to sum up:

The oracle version is 8.1.7 (yes it is old, yes it has reached EOL)

I have a table:
NUMBERS (startnumber(varchar2(8)) not null, endnumber(varchar2(8)) not null)

I do the query:
select * from numbers where '12345678' between startnumber and endnumber and even with a hint on startnumber, endnumber index, it performes bad.

Number of rows in the numbers table is 5-6 millions. There is a normal index on startnumber, endnumber (both asc) There is a function based index on substr(startnumber, 1, 4), substr(endnumber, 1, 4), startnumber, endnumber

And even if I do a select that uses the FBI it still performes bad, actually about 50% more bad :-(

You wrote that you might had any ideas or did i misunderstood you ?

Regards
Benjamin Received on Wed Jun 27 2007 - 15:19:18 CDT