Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4791

LIKE operator optimizations and concatenation

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: 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.

        Attachments

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

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: