Uploaded image for project: 'Ignite'
  1. Ignite
  2. IGNITE-13021

Calcite integration. Avoid full scans for disjunctive queries.

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: sql
    • Labels:
      None
    • Ignite Flags:
      Docs Required, Release Notes Required

      Description

      Currently a full table scan will be executed in the case of disjunctive predicate even if predicate fields are indexed. For example:

      SELECT * FROM emps WHERE name='A' OR surname='B'
      

      This is caused by the nature of indexes: they can return cursor bounded by lower and upper bounds. We can cope with it by implementing a logical rule for rewriting OR query to a UNION ALL query:

      SELECT * FROM emps WHERE name='A' 
      UNION ALL
      SELECT * FROM emps WHERE surname='B'  AND LNNVL(name='A')
      

      where LNNVL() function has semantics

      LNNVL(name='A') == name!='A' OR name=NULL.
      

      It is used to avoid expensive deduplication. This name is taken from Oracle, we can think of more meaningful name, or find the analog in Calcite or H2.
      See, for example, this blog post: https://blogs.oracle.com/optimizer/optimizer-transformations:-or-expansion for details.
      Also it is needed to check this works for IN clause with small number of literals (AFAIK Calcite converts large IN clauses to a join with Values table where N > 20).

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                amashenkov Andrey Mashenkov
                Reporter:
                rkondakov Roman Kondakov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 50m
                  50m