How to achieve better CLUSTERRATIO and CLUSTERFACTOR

Body

CLUSTERRATIO information indicates the degree to which the table data is clustered in
relation to this index. The higher the number, the better the rows are ordered in index key sequence.

So it is related to How the Table Data is Cluster in relation ship with Index.

Reorg table is main operation to archive same, But we need to give Index name to Reorg
command to cluster based upon that.
Refer:
https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0005305.html

If index clustering statistics are not available, the optimizer uses default values, which
assume poor clustering of the data with respect to the index. The degree to which the data
is clustered can have a significant impact on performance, and you should try to keep one
of the indexes that are defined on the table close to 100 percent clustered.

1)If we need achieve best possible CLUSTERRATIO and CLUSTERFACTOR its recommended to have Clustering Index.

Clustering Index:
We can use CLUSTER Keyword in the Create Index statement

Create Index Command: https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000919.html

CLUSTER keyword in Create Index impose
Specifies that the index is the clustering index of the table. The cluster factor of a
clustering index is maintained or improved dynamically as data is inserted into the
associated table, by attempting to insert new rows physically close to the rows for
which the key values of this index are in the same range.

2)How to Reorg to achieve better Clustering:

While performing Table Reorg we need to give Index name as input to Rerg table command to
cluster tabled based upon that Index.

i.e. INDEX index-name
Specifies the index to use when reorganizing the table.
The database manager uses the index to physically reorder the records in the table it is reorganizing.

For an in place table reorganization, if a clustering index is defined on the table
and an index is specified, it must be the clustering index. If the in place option is
not specified, any index specified will be used.

If you do not specify the name of an index, the records are reorganized without regard to order.

If the table has a clustering index defined, however, and no index is specified, then
the clustering index is used to cluster the table.

Refer REORG Table command:
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001966.html

 

3)Finally, If the DETAILED clause is not specified, only CLUSTERRATIO is collected.
So we need to used DETAILED cause while collecting Stat against Index after above operations.

So having Clustering Index or either specifying Index name in Table Reorg command we can achieve better Clustering.