Derby
  1. Derby
  2. DERBY-4791

LIKE operator optimizations and concatenation

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.7.1.1
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      All

      Description

      While queries of the form "<column> LIKE ' <string-constant>%' " are optimized into index scans, the equivalent expression using a concatenation "<column> LIKE ' <string-constant>' || '%' " would result in a table scan.
      Queries of the form "<column> LIKE ?" are optimizable using an internally generated parameter, so it doesn't seem far-fetched to generate such a parameter for a concatenation of strings (or other string expressions) as well, once its value has been calculated. This is of course limited to cases where the result of the string expression can be calculated once, i.e. it is independent of columns in the query.
      It is sometimes possible to work around this by manually adding the " x >= '<string-constant>' AND x < '<string-constant>\uffff...' " condition.

      1. fold-constants.diff
        6 kB
        Knut Anders Hatlen

        Issue Links

          Activity

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Uriah Eisenstein
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development