Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-20215

Add new index on REPO, NODE_PATH and DEPTH

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Normal
    • Resolution: Unresolved
    • Affects Version/s: 6.8.13
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None
    • Environment:

      Oracle DB

      Description

      We suspect the depth query is causing the load issue. Add another index on REPO, NODE_PATH, and DEPTH, because there are no indexes that exist that fully satisfies the where clause below:

      SELECT /*+ index(NODES NODES_REPO_PATH_NAME_IDX) */ * FROM nodes WHERE repo = :1 AND node_path = :2 AND depth = :3

      One of the variables in the where clause is depth and there is no index on depth so it is using the available index on repo, path, and name, and it needs to scan this bigger index. Also, there is a slow of skewness in the index.

      The suggestion is to use another index (path, repo, depth), note path is leading, so the index is more distributed. We have to try it for sure.

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            joshuah Joshua Han
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: