My thanks goes out to one of our blog readers, Alan Plummer, who crafted this query after attending the DBI Webinar. You should adjust the WHERE clause to suit your needs.
select char(a.tabschema,8 ) as schema,
char(a.tabname,18 ) as table,
char(a.indname,18 ) as index,
a.fullkeycard as IXFULLKEYCARD,
b.card as TBCARD,
int((float(a.fullkeycard)/float(b.card)) * 100) as ratio
from syscat.indexes a inner join syscat.tables b
on a.tabschema = b.tabschema
and a.tabname = b.tabname
where a.fullkeycard > 1
and a.tabschema <> 'SYSIBM'
and b.card > 100
and a.uniquerule <> 'U'
and int((float(a.fullkeycard)/float(b.card)) * 100) < 60
order by 1, 2, 3;
This query returns the table schema, table name, index name, index full key cardinality, table cardinality, and the index cardinality percentage of the table's cardinality, for indexes where the cardinality percentage is less than 60% (excluding the catalog tables which we can't do anything about).
75% or higher is an admirable goal. Unique indexes will have a 100% ratio. The higher the ratio, the less expensive the index will be to maintain on Inserts, Updates, and Deletes, and the more useful the index might be for quickly and efficiently retrieving result sets. Said another way, high quality indexes with a high cardinality percentage can help improve Index Read Efficiency (IREF).
This is not to say that indexes with a very low cardinality as a percentage of the table's cardinality will not be useful, or will not be used by the optimizer as part of the access plan. To my surprise, in one of my test workloads, the optimizer chooses to use an index with a cardinality of < 1% of the table's cardinality. Apparently when you are extremely thirsty even dirty pond water has some appeal, and so it is true with DB2 deciding that a crummy index is better than none. Still, the performance attributes are horrific compared to the results obtained with a better physical design.
If you haven't yet registered for DBI's free Webinar yet, please do so. We'd love to have you join us as we cover SQL workload analysis and index design topics. One lucky participant will be randomly chosen to win a $50 Amazon.com gift certificate. Visit www.Database-Brothers.com to register.
Have you placed your advance order for Phil Gunning's forthcoming book DB2 9 for Developers yet? Phil writes great DB2 references, and if you pre-order you can save 37% at Amazon.com.
Did you ever wonder how many calories are found in certain foods or ingredients? www.calorie-count.com has a wealth of information about foods, ingredients, calories, fats, and related nutrition information for just about anything you might put in your mouth. I say "just about anything" because I watched some of you at the IBM IOD conference last week. For example, here is the nutrition information for Hostess Ho Ho's - might as well grab a spoon full of Crisco shortening and dunk it in sugar. As for me, I'm enjoying Walnuts for a healthier snack.
Until next time,
Cheers,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info