Re: [Q]: Indexes with LIKE clause

In article <39oa9f$5e8_at_dcsun4.us.oracle.com> drbrown_at_us.oracle.com (Darin R. Brown) writes:
> neil_at_ms.uky.edu (Neil Greene) writes:
>
> >What ill effects does the use of a LIKE clause have with respect to
> >index performance, if any??
> >
> >-- Neil
>
> It depends on how you use a like and how your indexes are created. If
 you
> are performing a like on an indexed database field, placing a % on the
 end
> or in the middle will allow partial use of the index. Placing a % on
 the
> front will cause a full table sacn.
>
> EG: where emp.name LIKE 'BRO%' will use index
> where emp.name LIKE '%OWN' will not use index
> where emp.name LIKE 'BR%N' will use index to qualify the BR, then
scan

We found the oddity here. The base table which was being derived from several views was using a LIKE condition on a numeric value. This was causing Oracle to place a TO_CHAR in front of the field, hence, shutting down its use of the index.

Thanks!

-- 
Sincerely,

Neil Greene