Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    5

    Unanswered: Question on Clustered Index and Cluster Ratio for table

    All,

    I'm using DB2 9.7 on AIX.

    I have a table that has 7 columns (2 are Char(43), 2 are Integer, 2 are BigInt, and one is Blob. There is one index on the 2 primary key columns (Char43 and Int) and both are ASC. The table has over 300,000 rows

    The application is running the SQL statement below against the table several times a day

    SELECT SEQUENCE_COL, STORED_SIZE, EXPANDED_SIZE, STORAGE_METHOD, DELTA_PREDECESSOR, CONTENT_BYTES FROM SCM.CONTENT WHERE HASH_CODE = ? ORDER BY SEQUENCE_COL ASC

    2 questions:

    1. Would it make sense to create a clustered index on SEQUENCE_COL ASC? That column is currently part of the system-generated index mentioned above that is the only index currently on the table.

    2. When I run Reorgchk on the table's index the cluster ratio is 0. Even after running a reorg on the index and updating statistics it still says 0. Why would it still be 0 after running a reorg? Is this normal or expected?

    Thanks in advance for any advice.
    Last edited by miwiwa; 06-04-12 at 04:01. Reason: Add additional info.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    this is possible
    clusterratio reflects the organization of data compared to index
    if data is not organized at all against this index = clusterratio can be very low
    unload data according index - reload data
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    1、according to your query, the best index maybe is on columns ( HASH_CODE, SEQUENCE_COL )。
    2、plz show the exact reorg statement or try this:
    reorg table SCM.CONTENT index idx1( assume the idx1 is the index of which you want to improve the clustor ratio )
    and update statistics.

  4. #4
    Join Date
    Jun 2012
    Posts
    5
    Thanks you guys for the help. I ended up creating a clustering index, reorg-ing the table on that index, then Runstats.

    When I did reorgchk again the Cluster Ratio is now 100%.

    Prior to creating the clustering index I browsed the data in the table an saw that it was not ordered at all, which I assume explains the 0% cluster ratio.

Posting Permissions

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