Mike, I would like to understand the "you aren't gathering the plan with all options enabled" comment. I followed this from the Derby Tuning guide:
-- turn on RUNTIMESTATISTICS for connection: CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); -- execute complex query here -- step through the result set -- access runtime statistics information: VALUES SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(0); And placed my query in there. So if there is something else that needs to be done, can you point me in the correct direction. I am going to check the large table tonight and see if returns an error on that. How could the optimizer have the correct row counts? ________________________________________ From: Mike Matrigali [[email protected]] Sent: Wednesday, March 27, 2013 6:36 PM To: [email protected] Subject: Re: Why is the optimizer choosing such a bad path On 3/27/2013 12:16 PM, Bergquist, Brett wrote: > Some background. > > I have a customer that is using an earlier release of our system that has > Derby 10.8.2.1 installed. Because of issues like > https://issues.apache.org/jira/browse/DERBY-5680, > it has been running with the indexStat daemon disabled. > > We are going to have a new release soon and it will be installing Derby > 10.9.1.0 with the indexStat > daemon enabled. I recently got a copy of the customer's database (132Gb) > and ran into > a very long query. I manually ran SYS_UTIL.SYSCS_UPDATE_STATISTICS on all > of the tables > in the query to ensure that the statistics are up to date. > ... when I look at bad query plans i usually look for estimated row counts vs actual to see where the optimizer made some bad assumptions. The plan you posted has a bunch of 0's. So maybe you aren't gathering the plan with all options enabled? If optimizer actually thinks there are 0 rows in your 62 million row table that would be a problem.
