10-04-11, 06:24 #1Registered User
- Join Date
- Aug 2011
Unanswered: How can we increase CLUSTERRATIO of a regular index??
How can we increase CLUSTERRATIO of a regular index??
10-04-11, 06:35 #2Registered User
Provided Answers: 11
- Join Date
- Apr 2006
there can be only 1 ci
if the table is also organized by this index, the ratio will increase
in most tables - data is organized according ci and other indexes are not part of organization
they will be used for specific predicates
if you don't have ci - unload the data with order by the clmns from this index and reload data. data will be organized according the index and ratio will increase. the data will not be maintained according the indexBest 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
10-04-11, 14:33 #3Registered User
- Join Date
- Sep 2004
Just a simple example to clarify this:
suppose you have a table with two columns: country names and city names.
suppose that you define two indexes, one per column.
finally suppose that your table data is physically sorted on the city names, and then (for equal cities) on the country name.
The cluster ratio of the "city names" index will then be 100%, while it would be very unlikely that the cluster ratio of the other index is higher than 10% (unless there are very few different cities or countries): country names will appear in seemingly random order.
The other way around, and assuming several cities per country, the cluster ratio of the "cities" index *could* be around 50% or even higher, e.g. when rows would be physically sorted on city for equal countries. But you'll never get both indexes have cluster ratio 100%, unless there's just a single country, or in the very unlikely event when the first few characters of city names equal their country name.--_Peter Vanroose,
__IBM Certified Database Administrator, DB2 9 for z/OS
__IBM Certified Application Developer
__ABIS Training and Consulting