LIKE operator optimizations and concatenation

[Uriah Eisenstein <ur...@gmail.com>]

Hello,
I've been using Derby recently and have started to check the query plans for
my queries. I saw that while queries of the form "<column> LIKE
'<string-constant>%' " result in an index scan, even a trivial change such
as using two concatenated string constants in the pattern ("<column> LIKE
'<string-constant>' || '%', for instance) falls back to a table scan.
Similarly, "<column> LIKE ?" is optimized, but "<column> LIKE ? || '%' " is
not.
I'm not a DB expert (in particular regarding the back-end) so I want to know
whether there are any plans to improve this behaviour, or if not, would it
be possible - I'll file the issue in Jira then. In the DB table I've created
some strings are prefixes of others and I rely on it, so this may well
change the queries if not even the table design.
Thanks,
Uriah Eisenstein

Re: LIKE operator optimizations and concatenation

[Knut Anders Hatlen <kn...@oracle.com>]

Uriah Eisenstein <ur...@gmail.com> writes:

> Hello,
> I've been using Derby recently and have started to check the query plans for
> my queries. I saw that while queries of the form "<column> LIKE '
> <string-constant>%' " result in an index scan, even a trivial change such as
> using two concatenated string constants in the pattern ("<column> LIKE '
> <string-constant>' || '%', for instance) falls back to a table scan.
> Similarly, "<column> LIKE ?" is optimized, but "<column> LIKE ? || '%' " is
> not.
> I'm not a DB expert (in particular regarding the back-end) so I want to know
> whether there are any plans to improve this behaviour, or if not, would it be
> possible - I'll file the issue in Jira then. In the DB table I've created some
> strings are prefixes of others and I rely on it, so this may well change the
> queries if not even the table design.

Hi Uriah,

This sounds like a reasonable request, so feel free to log a JIRA
issue. Optimization of LIKE clauses happens in LikeEscapeOperatorNode,
where it for example rewrites

  x LIKE '<string-constant>%'

to

  x >= '<string-constant>' AND x < '<string-constant>\uffff...'

I don't think it should be too difficult to make it capable of doing
similar transformations on the examples you posted.

-- 
Knut Anders