Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Are You Using All Indexes of Your Database?

DZone's Guide to

Are You Using All Indexes of Your Database?

Free Resource

What if you could learn how to use MongoDB directly from the experts, on your schedule, for free? We've put together the ultimate guide for learning MongoDBSign up and you'll receive instructions for how to get started!

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.

What if you could learn how to use MongoDB directly from the experts, on your schedule, for free? We've put together the ultimate guide for learning MongoDBSign up and you'll receive instructions for how to get started!

Topics:
java ,sql ,database ,tips and tricks ,tools & methods ,index

Published at DZone with permission of Partha Bhattacharjee, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}