Re: fast search or scan for words in selected field

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Jim, it sounds like you're describing index-only-access, which can give 
significantly better performance. I don't know whether the optimizer has 
the smarts to do this yet for the LIKE predicate, haven't checked deeply 
enough. The limitation is, however, that all fields being returned must be 
in the index.

Vern

At 02:12 PM 9/10/2004, you wrote:
> (i may be wrong on this) but I thought if an index exists
> over the NAME field, and a ..where name like %JOHN%
> then sql will search all the entries in that index, which was still faster
> than reading that column in the file.
> jim
> ----- Original Message -----
> From: "Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>
> To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
> Sent: Friday, September 10, 2004 2:16 PM
> Subject: RE: fast search or scan for words in selected field
>
>
> > On LIKE keyword, index cannot be used if wildcard is used at the beginning
> > of the value, i.e. %MARTIN or %MARTIN%.
> > But if it is at the end of the value, i.e. MARTIN%, then index can be used
> > by the query optimizer.
> >