DZone
Database Zone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
  • Refcardz
  • Trend Reports
  • Webinars
  • Zones
  • |
    • Agile
    • AI
    • Big Data
    • Cloud
    • Database
    • DevOps
    • Integration
    • IoT
    • Java
    • Microservices
    • Open Source
    • Performance
    • Security
    • Web Dev
DZone > Database Zone > Are You Using All Indexes of Your Database?

Are You Using All Indexes of Your Database?

Partha Bhattacharjee user avatar by
Partha Bhattacharjee
·
May. 30, 14 · Database Zone · Interview
Like (1)
Save
Tweet
3.38K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Don't Underestimate Documentation
  • What Do Great Engineering Managers Need To Know About Compensation and Equity?
  • How to Handle Early Startup Technical Debt (Or Just Avoid it Entirely)
  • Testing Schema Registry: Spring Boot and Apache Kafka With JSON Schema

Comments

Database Partner Resources

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • MVB Program
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends:

DZone.com is powered by 

AnswerHub logo