Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-3790

Investigate if request for update statistics can be skipped for certain kind of indexes, one instance may be unique indexes based on one column.

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: 10.9.1.0
    • Component/s: Store
    • Labels:
      None
    • Bug behavior facts:
      Performance

      Description

      DERBY-269 provided a manual way to update the statisitcs. There was some discussion in that jira entry for possibly optimizing the cases where there is no need to update the statistics. I will enter the related comments from that jira entry here for reference.

      **************************
      Knut Anders Hatlen - 18/Jul/08 12:39 AM
      If I have understood correctly, unique indexes always have up to date cardinality statistics because cardinality == row count. If that's the case, one possible optimization is to skip the unique indexes when SYSCS_UPDATE_STATISTICS is called.
      **************************

      **************************
      Mike Matrigali - 18/Jul/08 09:48 AM
      is the cardinality of a unique index 1 or is it row count?

      It is also more complicated than just skipping unique indexes, it depends on the number of columns in the index because
      in a multi-column index, multiple cardinalities are calculated. So for instance on an index on columns A,B,C there are
      actually 3 cardinalities calculated:
      A
      A,B
      A,B,C

      I agree that the calculation of cardinality of A,B,C could/should be short circuited for a unique index.
      **************************

      **************************
      Knut Anders Hatlen - 18/Jul/08 03:25 PM
      Mike,
      It looks to me as if the cardinality is the number of unique values, so I think the cardinality of a unique index is equal to its row count (for the full key, that is). You're right that we can't short circuit it if we have a multi-column index. I don't know if it's worth the extra complexity to short circuit the A,B,C case, since we'd have to scan the entire index anyway. For a single-column unique index it sounds like a good idea, though.
      **************************

        Attachments

        1. derby-3790-1a-skip_stats_scui.diff
          9 kB
          Kristian Waagan
        2. derby-3790-1b-skip_stats_scui.diff
          55 kB
          Kristian Waagan
        3. derby-3790-1c-skip_stats_scui.diff
          55 kB
          Kristian Waagan
        4. derby-3790-2a-minor_test_improvements.diff
          7 kB
          Kristian Waagan

          Issue Links

            Activity

              People

              • Assignee:
                kristwaa Kristian Waagan
                Reporter:
                mamtas Mamta A. Satoor
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: