Uploaded image for project: 'OpenIDM'
  1. OpenIDM
  2. OPENIDM-13584

Improve Oracle relationship index

    Details

    • Target Version/s:
    • Support Ticket IDs:

      Description

      IDM 6.0 had a significant amount of improvements to the retrieval of relationships, however some of our customers are still using older versions. Based on a recent performance issue it was identified that the following index

       

      CREATE INDEX "FRIM_MAIN"."IDX_RELATIONSHIPPROPERT_4" ON "FRIM_MAIN"."RELATIONSHIPPROPERTIES" ("PROPVALUE", "PROPKEY") TABLESPACE "FRIM_MAIN_DAT";

       

      Caused some of the searches to go from 8s -> 300ms i.e. 

       

      SELECT obj.* FROM FRIM_MAIN.relationships obj INNER JOIN FRIM_MAIN.relationshipproperties firstId ON firstId.relationships_id = obj.id AND firstId.propkey = :"SYS_B_0" INNER JOIN FRIM_MAIN.relationshipproperties firstPropertyName ON firstPropertyName.relationships_id = obj.id AND firstPropertyName.propkey = :"SYS_B_1" INNER JOIN FRIM_MAIN.relationshipproperties secondId ON secondId.relationships_id = obj.id AND secondId.propkey = :"SYS_B_2" INNER JOIN FRIM_MAIN.relationshipproperties secondPropertyName ON secondPropertyName.relationships_id = obj.id AND secondPropertyName.propkey = :"SYS_B_3" WHERE ((firstId.propvalue = :1 AND firstPropertyName.propvalue = :2 ) AND (secondId.propvalue = :3 AND secondPropertyName.propvalue = :4 )) UNION ALL SELECT obj.* FROM FRIM_MAIN.relationships obj INNER JOIN FRIM_MAIN.relationshipproperties firstId ON firstId.relationships_id = obj.id AND firstId.propkey = :"SYS_B_4" INNER JOIN FRIM_MAIN.relations hipproperties firstPropertyName ON firstPropertyName.relationships_id = obj.id AND firstPropertyName.propkey = :"SYS_B_5" INNER JOIN FRIM_MAIN.relationshipproperties secondId ON secondId.relationships_id = obj.id AND secondId.propkey = :"SYS_B_6" INNER JOIN FRIM_MAIN.relationshipproperties secondPropertyName ON secondPropertyName.relationships_id = obj.id AND secondPropertyName.propkey = :"SYS_B_7" WHERE ((firstId.propvalue = :5 AND firstPropertyName.propvalue = :6 ) AND (secondId.propvalue = :7 AND secondPropertyName.propvalue = :8 ))

       

       

      This RFE is to recommend this index in the IDM 5.5 documentation and/or include with any patches. Will leave for engineering to decide:

      1. Is it a feasible advice to provide in IDM documentation
      2. Is it worth the effort to build into the product 4.5/5.5 or just refer to the documentation.
      3. Is it worth including in version 6.5+

       

        Attachments

          Activity

            People

            • Assignee:
              matthias.grabiak Matthias Grabiak
              Reporter:
              mark.nienaber@forgerock.com Mark Nienaber
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: