We have in our database an index with table cardinality over 10.000 but full key cardinality only 1. The reason for this that all the values for the column are NULL, hence cardinality 1. Does this index still perform badly in practice? Or is it omitted because the values are all NULL? Our database is IBM DB2 9.7.
Db2 – Database index on FK with only NULL values
db2index
Related Question
- Db2 – Should indexes automatically be placed into their own bufferpool
- DB2 DBM paramter NUMDB has value 8. Should I change it to 1 because I have only one database
- DB2 9.7 LUW – How to SET SESSION_USER when I am DBADM,SECADM
- Why DB2 Data Folder Size Changes (Increasing and Decreasing)?
- Db2 – Database returned to CIRCULAR mode but logs are still being archived to old location
- DB2 Index – Reusing Index Keys on GENERATE ALWAYS
- MySQL InnoDB – Performance for B+Tree Index with Many Duplicate Values
Best Answer
The only situation where an index with full key cardinality = 1 will help is when you are looking for something that does not exists. By using such index you can quickly conclude that nothing will match your predicate, whereas without the index you will have to scan the table.
If you have a large table and you frequently look for things that does not exists, you can benefit from such index (fairly rare situation).
The downside of such index is that the only leaf-page will have a long rid list that has to be maintained during modification of the table.
In most cases you will see an overall performance improvement by dropping that index, but it is impossible to tell for sure without having access to the system. Ideally you have a test system where you can run a representative load for your system and compare the performance with and without the index.
For the big picture, what is the purpose of the attribute that never exists? You may want to consider normalizing this table and move this attribute to a separate table.