> > When I use the query:
> >
> > select * from mytable where myfield like :myparameter
> >
> > and define myparameter = 'test'
> >
> > the plan doesn't use the index (natural) and my query returns slowly.
> >
>
>
> Once it's a parameter, the parameter could hold any value like:
> 'ABC'
> 'ABC%'
> '%ABC'
>
> the first two could use an index during a search the last on could not
> use any index.
>
> The optimizer shoul prepare the path for all the scenarios.
Of course, thank you for your answer. I create my queries dynamically each time
and thought the use of parameters would be less problematic (escaping etc.).
>
> You could change your query to something like:
>
> select * from mytable where myfield starting with :myparameter
>
> This way an index would be used.
>
> In fact in a non parameterized query, your original query has a kind of code
> injection, and become something like:
>
> select * from mytable where myfield like 'test%'
> and myfield starting with 'test'
>
> Thus the index could be used.
>
Yes, that's a possibility. But I think I still need to create the statement
without parameters for string values to give my users the maximum flexibility
and performance. Once again, thank you for you answer.
