Unanswered: Question on Clustered Index and Cluster Ratio for table
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
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 03:01.
Reason: Add additional info.
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
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.