Hello Zdenek,

At 08:59 AM 22/04/2004 +0000, you wrote:

>Hello,
>I have stored procedure with VARCHAR parameter. And I have table
>PATHS with indexed column PATH_VALUE (VARCHAR).
>
>Inside procedure is following statement
>
>UPDATE PATHS
> SET PATH_DELETION = NULL
> WHERE PATH_VALUE LIKE :PARAMETER;
>
>Now I call this procedure
>
>EXECUTE PROCEDURE NAME('\\path\hsjha\%');
>
>index is not used and table PATHS is sequentialy readed.
>
>If I execute statement directly
>
>UPDATE PATHS
> SET PATH_DELETION = NULL
> WHERE PATH_VALUE LIKE '\\path\hsjha\%';
>
>index is used and performance is much better.

The index is used here because there is a single wildcard at the end of the
string and the optimizer converts it to STARTING WITH, which uses your index.

But LIKE '%STRING%' can never use an index.

Inside the sp, you have

LIKE :PARAMETER

As others have explained, at the time of the optimization, the optimizer
simply can not consider using an index, because the only assumption it can
make is "worst case" - a normal LIKE '%STRING%'.

If you are *always* going to pass 'string%' in parameter, then use STARTING
WITH and it will automatically consider the index.

/heLen