Re: simple sql using between startnumber and endnumber not performing

On 27 jun, 22:19, Benjamin Sølberg <benjamin.soelb..._at_gmail.com> wrote:
> 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.
>

Which part of " (If it's a string, I'd make you rename the columns in the design!) "
did you not understand?!?

Do us a favor: take a copy of your table: create table frank as select to_number(startnumber) "STARTNUMBER" , to_number(endnumber) "ENDNUMBER" from
numbers;

Now rerun you between query with numbers (meaning: same statement, no quotes).

Explain the difference in speed.

Due, monday, before 09:00.

:) Received on Thu Jun 28 2007 - 14:10:02 CDT