Last week, I spent few hours analyzing Index related data in DB2 LUW’s catalog table syscat.indexes and I found something very interesting. In a nut shell, what I learnt is that catalog data that relates to an index’s cardinality in a DPF database is misleading!! Seems like a bold statement..Please read on…
Index Cardinality? What is it? Here is the basic concept in brief. Index Cardinality is defined as the number of distinct values of column(s) that make up a given index. Index Cardinality for an index I1 defined on column C1 on a table S1.T1 is found by querying syscat.indexes:
db2 “select count(distinct(C1)) from syscat.indexes where tabschema = ‘S1’ and tabname = ‘T1’ with ur”
But, why should you pay attention to Index Cardinality? If you are not sure, you should watch Ember Crooks’s awesome DB2Night Show on (here is the link) April 18th about “why low-cardinality indexes negatively impact performance”. As the title says, low-cardinality indexes negatively impact performance and so it is important to “measure” cardinality of indexes in your database and potentially drop low -cardinality indexes after some research. This blog demonstrates the need to do additional calculations to understand the quality of indexes in a DPF database.
In a single partitioned database, if the stats are up-to-date, the above SQL is all you would probably need to understand an index’s cardinality but in a DPF database the process is a big tricky because the catalog data for indexes that do not have primary key columns in them seems to be incorrect. I will explain with an example.
Consider a table DB2INST1.EMPLOYEE that is created on 12 out of 13 nodes in a DPF database. Essentially, this would have been done in 3 steps a) create a partition group with 12 partitions in it b) create a tablespace in this partition group and c) create a table in this tablespace.
CREATE TABLE DB2INST1.EMPLOYEE (
CONTACT_ID BIGINT NOT NULL ,
EMPLOYEE_LAST_UPDATE_TMSTMP TIMESTAMP NOT NULL ,
EMPLOYEE_NAME VARCHAR(255) NOT NULL
DISTRIBUTE BY HASH(EMPLOYEE_GK)
IN EMPLOYEE INDEX IN EMPLOYEE_IX ;
— DDL Statements for Primary Key on Table DB2INST1.EMPLOYEE
ALTER TABLE DB2INST1.EMPLOYEE ADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_GK);
— DDL Statements for Indexes on Table DB2INST1.EMPLOYEE
CREATE INDEX DB2INST1.I1_EMPLOYEE ON DB2INST1.EMPLOYEE (CONTACT_ID ASC, EMPLOYEE_GK DESC);
CREATE INDEX DB2INST1.I2_EMPLOYEE ON DB2INST1.EMPLOYEE (EMPLOYEE_LAST_UPDATE_TMSTMP DESC);
CREATE INDEX DB2INST1.I3_EMPLOYEE ON DB2INST1.EMPLOYEE (CONTACT_ID ASC);
An attentive DBA would frown at me because I created a redundant index I3_EMPLOYEE… but please bear with me while I explain my observations. I wouldn’t create this redundant index in a real database.
Assuming stats are up-to-date (I usually do distribution on all columns and detailed indexes all), the following SQL when run would give index cardinality info for a specific table. The last column (INDEX_CARD_PERCENT), a derived value, is index cardinality percentage. This column summarizes the index cardinality compared to table’s cardinality. The closer it is to 100, the better the quality of index is.
select char(indname, 10) as indname, fullkeycard, b.card as table_card, decimal(float((a.fullkeycard)/float(b.card))*100,5,2) as index_card_percent from syscat.indexes A inner join syscat.tables B on A.tabschema=B.tabschema and A.tabname=B.tabname where A.tabschema = 'DB2INST1' and A.tabname = 'EMPLOYEE' with ur
INDNAME FULLKEYCARD TABLE_CARD INDEX_CARD_PERCENT ---------- -------------------- -------------------- ---------------- PK_EMPLOYEE 10361208 10361208 100.00 I1_EMPLOYEE 10361208 10361208 100.00 I2_EMPLOYEE 430632 10361208 4.15 I3_EMPLOYEE 863434 10361208 8.33
4 record(s) selected.
Evidently, index cardinality percentage is 100% for PK_EMPLOYEE and I3_EMPLOYEE. Data from syscat.indexes looks right.
100% Index Cardinality percentage seems to hold the truth because:
a) Primary Key (PK_EMPLOYEE) because EMPLOYEE_GK column is distinct and
b) Index I1 (I1_EMPLOYEE) because the column CONTACT_GK is actually unique (It was not explicitly declared unique to be able to accommodate anomaly data coming into DWH from various data sources). Combination of two distinct columns CONTACT_GK and EMPLOYEE_GK is unique.
Now to the most interesting indexes, I2_EMPLOYEE and I3_EMPLOYEE for which the catalog data seems to be incorrect..
Let us look at I3 first. As CONTACT_GK is unique through out the table, I would expect the index cardinality percentage for I3_EMPLOYEE to be 100% but from catalog data, it is 8.33. Obviously, this is incorrect. I opened a PMR with IBM to investigate this. I was told to multiply FULLKEYCARD by no. of partitions in which the table is on to get the correct FULLKEYCARD value.
In essence, corrected_fullkeycard = FULLKEYCARD * 12 = 863434 * 12 = 10361208.
Now, the corrected index cardinality percentage would be: 10361208/card = 10361208/10361208 = 100%.
We could also multiply 8.33 (original index cardinality percentage) by 12 to get corrected index cardinality percentage. 8.33*12 = 99.96 ~ 100%.
Let us take a similar approach to I2_EMPLOYEE.
For I2_EMPLOYEE, corrected_fullkeycard = 431644*12/10389756*100 = 49.85%
This could be cross-checked by actually calculating the count of distinct values for EMPLOYEE_LAST_UPDATE_TMSTMP and then dividing this number by row count from the table.
db2 “select count(distinct(employee_last_update_tmstmp)) from db2inst1.employee with ur” = 4467266
db2 “select count(*) from db2inst1.employee with ur” = 10361208
4467266/10361208*100 = 43.11%
You would notice that numbers (49.85% and 43.11%) still do not match. This is because: when runstats on a table are done in a DPF environment, DB2 only performs this operation on the first partition on which the table is on unless an explicit connection is made to a specific node on which the table is. In many cases if the column values are skewed, stats on a specific partition do not epitomize stats on the remaining partitions and this leads to mismatch between actual calculations and calculations from catalog data.
1) I attempted to demonstrate the need to do additional calculations before deciding on dropping indexes solely based on Index cardinality percentage data from catalog tables.
2) To get correct FULLKEYCARD values, for indexes that do not have unique or primary key columns, multiply FULLKEYCARD from syscat.indexes by no. of partitions on which the table is on.
Here is a SQL for DPF database to get corrected FULLKEYCARD and Index Cardinality Percentage values:
AS (SELECT ngname,
COUNT(*) AS ngcount
GROUP BY ngname)
SELECT CHAR(indschema, 10),
CHAR(indname, 18) AS indname,
fullkeycard * ng.ngcount AS
b.card AS tbcard,
Decimal(Float(( a.fullkeycard ) / Float(b.card)) * 100, 5, 2) AS
WHEN Decimal(Float(( a.fullkeycard * ngcount ) / Float(card)) * 100, 10, 2)
THEN Decimal(Float(( a.fullkeycard ) / Float(b.card)) * 100, 5, 2)
ELSE Decimal(Float(( a.fullkeycard * ngcount ) / Float(card)) * 100, 10, 2)
CHAR(colnames, 70) AS colnames
FROM syscat.indexes A,
WHERE A.tabschema = B.tabschema
AND A.tabname = B.tabname
AND B.index_tbspace = C.tbspace
AND ng.ngname = c.ngname
AND A.tabschema = ‘SCHEMA’
AND A.tabname = ‘TABLE’
Hope that helps.