db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: LIKE operator optimizations and concatenation
Date Mon, 16 Aug 2010 14:02:52 GMT
Uriah Eisenstein <uriaheisenstein@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 ? || '%' "
> 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>%'


  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

View raw message