RE: fast search or scan for words in selected field

[Frank.Kolmann@xxxxxxxxxx]

Dan to ignore case try this
SELECT * FROM cositm WHERE UCASE(ITDS) like '%CLIP%'

Guess I am just an inveterate TROLL but Joe try doing
that with CHAIN. (I wont reply to any response to this jibe :).

Frank Kolmann


>date: Fri, 10 Sep 2004 19:15:42 -0400
>from: "Dan Bale" <dbale@xxxxxxxxxxxxx>
>subject: RE: fast search or scan for words in selected field
>
----CLIPPED ---
>So, let's try 1,000,000 records, record length 343, ITDS is 30 characters
in
>length:
>   SELECT * FROM cositm WHERE ITDS like '%CLIP%'
>Instant return, so I try:
>   SELECT count(*) FROM cositm WHERE ITDS like '%CLIP%'
>This time, it takes 6 seconds to return a count of 38,860.  Change it to
>'%I%', same amount of time returns a count of 721,835.  The optimizer
>recommends building an index on ITDS, but don't know if that will help
when
>starting with % wildcard.
>I think I'll be tinkering with this.
>I need to find out how to ignore case, and I'll be rolling.
>
>db