Results 1 to 5 of 5

Thread: Cluster ratio

  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: Cluster ratio

    HI All,
    I m interested to know what DB2 Cluster Ratio is ? Eagerly expecting reply regarding the same

    Regards
    eureka

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Cluster ratio

    When you create an index as clustered, db2 tries to maintain the rows in the table in the same order as the index as much as possible. But as the data grows and rows are displaced, clustering does not succed in maintaining 100% ordering with the table ...

    Now, The degree of data clustering of the index, represented as a percentage between 0 to 100 is the cluster ratio ...

    ie, if 10% of rows are not in clustered fashion, the clusterratio is 90% ...


    HTH

    Sathyaram


    Originally posted by eureka
    HI All,
    I m interested to know what DB2 Cluster Ratio is ? Eagerly expecting reply regarding the same

    Regards
    eureka
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    To add to what Sathyaram said, the cluster ratio changes to 100 (%) when you reorg the table. In order for the current cluster ratio to be updated in the catalog tables, use the runstats command.

    If your cluster ratio is frequently lower than 90%, and you cannot do frequent reorgs to fix this (because of concurrency problems with your application), then increase the percent free of the table and the indexes. This will allow room (in-between reorgs) for DB2 to maintain the clustering sequence during inserts.

  4. #4
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    37
    Further on this topic...

    Clusterratio has also the very important role of influencing the optmizer when it comes to creating the access plan for an SQL statement.

    As Sathyaram was saying, the higher the index's clusterratio, the more rows are arranged in the same physical sequence as the index. The optimizer will prefer less jumping around, therefore, will lean towards choosing an index whose clusterratio is high. In reverse, it means, the optmizer may reject an index the developer would want to use (for reasons of good key coverage for a query) if the clusterratio is too low, sometimes resulting in tablespace scans even if there are is a possible index.

    One other thing. As Marcus mentioned, the clusterratio value in the index's catalog entry is set by RUNSTATS. However, this value is editable making certain indexes more or less attractive to the optmizer than they actually are, forcing it to choose different indexes than it normally would. This, of course, is not where one would start if applications are not performing well.

    Cheers,

    Julius

  5. #5
    Join Date
    Sep 2003
    Posts
    7

    Talking

    Thanks sathyaram_s , Marcus_A , jsasvari for your reply on Cluter Ratio. that was very useful......


    eureka

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •