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 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
