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

Indexes with NULL values are not used on Oracle

    XMLWordPrintable

    Details

    • Type: Task
    • Status: Open
    • Priority: L3 - Default
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: engine
    • Labels:
      None

      Description

      Steps to do:
      1) Replace all keys with composite keys e.g. MY_KEY_ -> (MY_KEY_, 0)
      2) Set up an Oracle Database and check, if all test are running flawlessly

      Problem:
      Assuming one has column index on ( x ) and one uses WHERE x IS NULL, then Oracle does not use an index. The reason is that Oracle indexes do not contain values for rows with a NULL index key. The issue is described in depth at this blog and this forum thread.

      Possible Solution:

      In order to fix that issue keys at the index creation should be containing a composite index e.g. USER_ID_ should be replaced by (USER_ID_, 0). For the Tenant ID this issue is treated on CAM-6680. However, this problem might also occur on other indexes. Therefore, all indexes should consist of composite indexes. 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).

      Hint:
      The files to be adjusted can be found here.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              johannes.heinemann Johannes Heinemann
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: