Re: [GENERAL] GEQO and KSQO problem.

At 11:45 +0300 on 02/09/1999, Natalya S. Makushina wrote:

> where CLIENTS.CLIENTID=SOTRUD.CLIENTID and
> ( CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (NOT
>CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%ruslanmr(at)hotmail(dot)com%')
> OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not
>CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%matukin(at)hotmail(dot)com%')
> OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not
>CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%knirti(at)kaluga(dot)ru%')
> OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not
>CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk(at)vniicom(dot)vsu(dot)ru%')
> OR CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not
>CLIENTS.ARH) and lower(SOTRUD.EMAIL) LIKE lower('%avk(at)vniicom(dot)vsu(dot)ru%')
> )
> order by CLIENTS.NEW_F, CLIENTS.NAME_1"

I wonder if this is all necessary? Can't you take the part

CLIENTS.PRINADL=PRINADLEG.PRINADL and CLIENTS.FLG_MY and (not CLIENTS.ARH)

Outside the parentheses and leave only the LIKE comparisons inside? Also,
there is no point in running "lower" on a string which is known in advance
to contain only lowercase letters, which is true for most literal strings
(If your application creates this, you can always do the conversion on the
client side before putting it into the query). It only leaks memory.

Thus, if you try to rewrite the WHERE clause as follows, do you get any
improvement?

where CLIENTS.CLIENTID=SOTRUD.CLIENTID
and CLIENTS.PRINADL=PRINADLEG.PRINADL
and CLIENTS.FLG_MY
and not CLIENTS.ARH
and ( lower(SOTRUD.EMAIL) LIKE '%ruslanmr(at)hotmail(dot)com%'
or lower(SOTRUD.EMAIL) LIKE '%matukin(at)hotmail(dot)com%'
or lower(SOTRUD.EMAIL) LIKE '%knirti(at)kaluga(dot)ru%'
or lower(SOTRUD.EMAIL) LIKE '%avk(at)vniicom(dot)vsu(dot)ru%' )
...

I think the optimizer would be most happy if you avoid the OR altogether by
using alternatives in a regular expression instead of like. This will also
allow you to use case insensitive comparison and give up the 'lower':

where CLIENTS.CLIENTID=SOTRUD.CLIENTID
and CLIENTS.PRINADL=PRINADLEG.PRINADL
and CLIENTS.FLG_MY
and not CLIENTS.ARH
and SORTUD.EMAIL ~*
'ruslanmr(at)hotmail\\(dot)com|matukin(at)hotmail\\(dot)com|knirti(at)kaluga\\(dot)ru|avk(at)vniicom\\(dot)v
su\\.ru';

Note that you have to put two slashes before each period in the string,
because a period is special in regular expressions.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma