Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Performance: Index Cardinality

April 7, 2009, 9:46 pm
Posted by Scott in General
In DBI's Webinar "DB2 LUW Index Physical Design & DBI Performance Solutions: Your Roadmap to Becoming a Performance Hero", we discuss some important index physical design guidelines and techniques (the next Webinar is October 30th @ 10:30am CDT). Ideally, the FULLKEYCARD cardinality of an index should be at least 75% of the table's cardinality. Here is a sample SQL query that can help you do a quick cardinality check on your indexes...
SQL to test Index Cardinality

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.

The Shameless Marketing Moment

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 gift certificate. Visit 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

Just for Fun

Did you ever wonder how many calories are found in certain foods or ingredients? 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,

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant

Printer friendly