Sorry - I feel like this is a newb question, but I can't find anything on it. In optimizing a query, I was able to apparently reduce the total timerons from 3,700 to 38. However, on closer examination of the explain plan for the optimized query, I see one IXSCAN that reports 3,707.97 timerons, while the total is still reported as 38.61!

I'm not super familiar with DB2 LUW, and was wondering how this could be. Even with parallelism, I would not think we could get much smaller than, say, 1/4 of the largest #. Surely not 1/10th. (?)

This is DB2 LUW 9.5 on AIX. Very plain-vanilla installation for testing environment. No HA, no partitioning, nothing like that.

Explain plan from Control Center - Command Editor

  • Did you try using db2exfmt to display the plan? – mustaccio Feb 7 '14 at 0:28
  • I have, now :-) It appears to give the same results. I'm having trouble figuring out how to attach a file, so here are the highlights: RETURN: (Return Result) Cumulative Total Cost: 38.6147 IXSCAN: (Index Scan) Cumulative Total Cost: 3707.97 – Joe Hayes Feb 10 '14 at 15:45
  • Normally this should not be so. There is a chance that the table and index statistics are out of sync (the db2exfmt report would show the respective data at the end). There is also a chance that there may be a bug in the explain facility. Are you using the most recent fix pack? – mustaccio Feb 10 '14 at 16:08
  • Any traction on this? – Chris Aldrich Feb 11 '14 at 13:40
  • Sorry - been a crazy couple days. I think you might be onto something about the statistics. Let me check it out and get back to you. The FP won't be the most recent (it's a client-model), but it should have been updated from the base. The main thing I think I was looking for is confirmation that those results are crazy and not to be trusted, and it sounds like I'm getting that. Thanks :-) I'll check the statistics and update the thread. – Joe Hayes Feb 11 '14 at 20:12

Just want to tie this off. As was suggested in the comments, this simply should not be so. It must have been a bug in the level of DB2 I was using.

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.