Are You Using All Indexes of Your Database?

It is an odd week at work when I don't learn something new. However, every once you learn something so obvious that you wonder exactly why did you miss this so long. This note is about one of those embarrassing "aha" moments.

I have never worked on a project that did not have some database behind it. DB2, Oracle, Sybase etc all the usual suspects. However, I have always had one or two members in the team who were the db nerds. While I wrote queries, stored procs etc etc when things got difficult with db related issues, they stepped up and I was only too happy to hand it over to them. This is all good. I like to play to the strengths of the team. However, this means I did miss a few low hanging fruits on the way. And here I talk about one such.

So, we delivered this nice project and we were doing a soft launch. As usual our DB2 came under fire. Someone asked a innocuous question. Are all our heavy lifting queries using indexes? Given the number of queries that we have it was not a quick investigation. So, the next question was are all the indexes being used? Or some of them are just hanging around eating resources and not doing anything at all. Surely I knew the answer.

Umm, no, I did not. I should have. This is something that I should have known. Not only for this application after someone asked but for all the applications that I have ever worked with. But the fact is I did not. I suspect strongly that many of the readers are in the same boat. And hence this half rant, half "how-to" note.

You have read the rant part already. Now is the "how-to" part.

Which indexes of your DB2 database are not being used?

    select   
     INDSCHEMA,   
     INDNAME,   
     TABSCHEMA,   
     TABNAME,   
     INDEXTYPE,   
     LASTUSED  
    from SYSCAT.INDEXES  
    where INDSCHEMA = 'yourschema'  
    order by LASTUSED ;   

This should give you a list of all indexes of your database in the order of your last usage. In case they are not used at all the LASTUSED will be '0001-01-01'. There you go. As simple as that. You will get a much more knowledgeable note on this subject at this link. That's all for today. If you liked what you read or not liked :) it will help if you leave a comment here.