Hi Brett, We have encountered and fixed at least one bug in case of single-column unique index, where starting 10.9 we have decided not to keep stats for such indexes. The bug fixed is DERBY-6045 and it is in Derby 10.10.1.1(revision 1480153). I was wondering if you could share java org.apache.derby.tools.sysinfo so we know if you have that fix.
If you do have that fix, then it is possible (though I am not certain) that there may be other bug in this area that you are running into. One way to figure that out is to use the property derby.storage.indexStats.debug.keepDisposableStats=true. This property will force Derby to collect the stats for single-column unique index. So, if you can recreate your db with this property and run the query, we can check the query plan to see if it starts using the single-column unique index. It might be possible to use existing db with this property and recreate the stats and try the query if creating the db from scratch with this property is a time consuming process. thanks, Mamta On Wed, Aug 21, 2013 at 4:55 PM, Bryan Pendleton <[email protected] > wrote: > Any thoughts or comments on why this the optimizer chooses on over the >> other? >> > > Unfortunately, no. > > But there have been concerns about the optimizer's cost > analysis for years; see > https://issues.apache.org/**jira/browse/DERBY-1905<https://issues.apache.org/jira/browse/DERBY-1905> > and some of the other issues linked from it (DERBY-1259, DERBY-1260). > > So, while I know it's frustrating for you to be wrestling with > an optimizer that won't behave, it's also good news, for Derby, > that you've made such good progress in trying to understand > this peculiar behavior. > > Perhaps when we get to the bottom of the strange behavior > you're seeing, we'll have a better understanding of some of > the other strange optimizer behaviors we've seen in the past. > > thanks, > > bryan > > >
