I am working on some IBM web application and it has one query hard coded in some part of the application which I cannot change or optimize - so modification of the SELECT statement is not possible. This is DB2 database.

Just to mention one important thing that query always returns 0 or 1 ROW!!!

Query is:

select * 
from workorder  
where parent= '682845'  
and siteid= 'NY'   
and woclass in ( 'WORKORDER' ) 
order by wosequence, taskid

Can someone please help me with definition of the proper index for this because this query is slowing the application too much and customer has big complaint.

I am not sure if I create index with three columns PARENT,SITEID,WOCLASS it will help because of the order by on which I do not have influence_ Thanks

  • Hi ypercube thanky you for envolvment. It always return one record sorry I forgot to mention that – Dejan Nov 4 '16 at 7:04
  • order by is always fixed it has those two columns so you think those 3 columns in INDEX would help regardless if it has two additional columns in the ORDER BY? – Dejan Nov 4 '16 at 7:06
  • Have you tried creating indexes and using EXPLAIN to see if DB2 uses them? No answer you're going to get here will substitute for what you get if you explain the query in your actual environment. – Ian Bjorhovde Nov 4 '16 at 16:30
  • Hi Ian, thank you as always for being so kind to help. I have not yet created index because of is live production system and I cannot stop application until we agree with customer when this will be possible. That is why I want to be sure I should add this index with 3 columns and then to test it, correct? Currently, without this index EXPLAIN query returns TABLESCAN then SORT and then TABLESCAN again. Should I add index with 3 columns? Please confirm because I was not sure because of the ORDER BY columns. As I mentioned this query returns 0 or 1 value. – Dejan Nov 5 '16 at 5:31
  • Additionally similar issue is on this link please assist me. Thank you!!! dba.stackexchange.com/questions/154338/… – Dejan Nov 5 '16 at 5:31

A 3-column index on (parent, siteid, woclass) will most probably improve the efficiency of the query.

It will certainly help to avoid the table scan. Most likely, with conditions on 3 columns, the rows that match will be very few or at least a very small percentage of the table and an index seek will be far more efficient than a table scan.

Even better, if the number of rows is small, the required sort will be efficient as well so you won't need to include the order by columns in the index (which may not be an option anyway, as the order by could be arbitrary, for all we know). If the query always returns 1 row, as you say, you certainly don't need the additional columns in the index. Why is there an ORDER BY in the query, if that's the case?

So, test the behaviour (of the query but also the overall behaviour of the applications and other queries) in your production/stage environment after you add the index and if the efficiency gain is good enough, roll the change in production.

(By the way, I'm not 100% sure about DB2 but you don't need to stop anything to add an index in most DBMS, I don't think you'd need it in DB2 either. You might get some locking off course and applications could be interrupted due to the locks, especially if the table is big, and various DBMS have different options to address these side effects. So it might be good to add the index in some "quiet" period, say between 04:00 and 06:00 that you know the database / applications have the least usage.)

  • 1
    No, you don't need to stop DB2 in order to apply an index. Or to run RUNSTATS. Or REORGs. It might lock the table to do so, but nothing has to be brought offline for any of these. (There are caveats of course, but just generally speaking.) – Chris Aldrich Nov 7 '16 at 13:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.