Re: Help - PL/SQL and the LIKE clause

>Hi,
>
>If you code a select statement in PL/SQL where the LIKE clause uses a
>variable instead of a literal (example WHERE COL1 LIKE VAR_NAME vs.
WHERE
>COL1 LIKE 'VALUE%'), even though the value in the variable does not have a %
>in the front of the value, the optimizer does a full table scan instead of an
>index scan.
>
>
>Does anyone know a trick to tell the optimized to do an index scan short of
>using an INDEX hint?

Change your query to something like

WHERE COL1 > VALUE||' ' AND COL1 < VALUE||'~'

(if you want to be really picky use the ASC(0) and ASC(128) to add those characters instead what I think are the 1st and last printable characters) This will have the same effect as the LIKE because all values will fall between these, and I believe a bounded range uses the index.

Dan Hekimian-Williams Received on Fri Nov 20 1998 - 21:14:41 CST