Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-6680

Tenant ID indexes are not used on Oracle for WHERE TENANT_ID_ IS NULL queries

    XMLWordPrintable

    Details

    • Type: Bug Report
    • Status: Closed
    • Priority: L3 - Default
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 7.6.0, 7.6.0-alpha4
    • Component/s: engine
    • Labels:
      None

      Description

      When users to not employ the marker-based multi-tenancy feature, then we add to every query by default the predicate WHERE TENANT_ID_ IS NULL (note that this can be avoided by overriding the engine configuration property tenantCheckEnabled).

      It appears that Oracle indexes do not contain values for rows with a NULL index key (or for composite keys, where all columns are NULL), see https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2458637000346464034 and https://tkyte.blogspot.de/2006/01/something-about-nothing.html.

      We can consider creating the indexes as composite indexes with keys like (TENANT_ID_, 0). That could avoid full table scans for queries where the other predicates do not require a full table scan (e.g. because they are indexed themselves).

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              thorben.lindhauer Thorben Lindhauer
              Reporter:
              thorben.lindhauer Thorben Lindhauer
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: