Re: oracle/sql "where clause"

g wayne nichols wrote:
>
> In <johnv.0a4n_at_acix.DIALix.oz.au> johnv_at_acix.DIALix.oz.au (John Verhoeven) writes:
>
> >In article dated Thu, 26 Sep 1996 06:23:00 +0100, AMARENDRA B NETTEM (nettama_at_charlie.cns.iit.edu) wrote:
> >> Paul Chow wrote:
> >> > does anyone know whether one of the following two where clauses is more efficient
> >> > than the other ...
> >> >
> >> > [1]. select ...
> >> > from ...
> >> > where (field_x='K' or field_x='L' or field_x='M')
> >> >
> >> > [2]. select ...
> >> > from ...
> >> > where field_x in ('K','L','M')
 

> >> Second query is efficient than the first one. But make there are no NULL
> >> value for that field when using IN operator.
 

> >Nope. Neither is more efficient than the other.
 

> >The second may be quicker for you to write, but the parser converts it
> >into the first form for execution anyway.
 

> >Do an explain plan on both queries and you will see that they are treated
> >the same.
>
> OK, then how about:
>
> [3]. select ...
> from ...
> where INSTR('KLM', field_x) > 0
> --
> ------------------------------------------------------------------------------
> Computer General Rochester, NY (716) 436-6372
> "... providing general computer solutions to specific business problems"
> gwn_at_servtech.com

Hi all

Everything that was writed is true, but ... If you table have a lot of records (gt 100000) [1] is more efficient than [2] and [3].
Reason: [2][3] requires additional parsing from optimizer. The solution [3] with rule-based analysis, always suppress the index usage, if it exists.

-- 
I hope this help         +----------------------------------------+
                         | A.Jorge Florindo              PORTUGAL | 
  Regards                | E-Mail: ajf.63_at_mail.telepac.pt         |
   Jorge                 +----------------------------------------+