db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: I need help on getting Derby to use a primary key index on a query
Date Fri, 23 Aug 2013 17:10:26 GMT
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 <bpendleton.derby@gmail.com
> 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
>
>
>

Mime
View raw message