MVSFORUMS.com

[sumithar]

Hi,
Is there some recommended value for cluster ratio of an index? I know only one index can be clustered and its value should be pretty much equal to 100 or very close.

but for the others are there any guidelines, like at least 50%? I understand it depends on the columns in the index. So in the case of the classic telephone directory example, an index on last name, first name is bound to have a cluster ratio pretty much equal to 100 but an index on street name could be all over the place.

Can a DBA do anything to improve the cluster ratio if its value is like 20%, say?

Thanks...

[kolusu]

Sumithar,

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. If table rows are in close to index-key sequence, rows can be read from a data page while the page is in the buffer. If the value of this column is -1, the optimizer uses PAGE_FETCH_PAIRS and CLUSTERFACTOR information, if it is available. The PAGE_FETCH_PAIRS column contains pairs of numbers that model the number of I/Os required to read the data pages into buffer pools of various sizes, together with CLUSTERFACTOR information. Data is collected for these columns only if you invoke the RUNSTATS command against the index, specifying the DETAILED clause.

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. In general, only one index can be one hundred percent clustered, except when the keys for an index represent a superset of the keys for the clustering index, or when there is an actual correlation between the key columns of the two indexes.

When you reorganize a table, you can specify an index that will be used to cluster the rows and keep them clustered during insert processing. Because update and insert operations can make a table less clustered in relation to the index, you might need to periodically reorganize the table. To reduce the number of reorganizations for a table that experiences frequent insert, update, or delete operations, specify the PCTFREE clause on the ALTER TABLE statement.

Kolusu

[sumithar]

Thanks Kolusu for a detailed response. I am sorry I didn't reply earlier but somehow the notification that a reply was posted didn't work.

[kolusu]

[sumithar]

Well, it seems to have sorted itself out now...I got this response and your other one to the FM/DB2 correctly!