db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Simon <simonzm...@gmail.com>
Subject Re: Execution Plan Changes Dramatically between Executions
Date Sun, 04 Jul 2010 02:19:27 GMT
Hi Mike,

Thanks for reply.  I have actually never run compress on the database
at all since it was created, so perhaps some stats are just miles off.
  All the tables concerned either have plenty of rows or are unchanged
between times when I observe the issue to occur & not.   I've done it
now and will see what happens.  Unfortunately the issue is rare enough
that I'll have to wait a few days to be sure about whether this helped
or not.   Even if it does help, I'm still kind of confused about why
it would happen.

I would love to know (from anybody who has insight):  is there any
case where other concurrent activity on the database (say, locking of
a table / rows, etc.) could cause Derby to not use an index?

The interesting thing is that when I run this query manually with the
database off line I can never make it happen.  However when it is
active (simultaneous activity going on at the same time) I see it
regularly.  I can't prove this isn't just luck / coincidence, but it's
enough of a correlation to make me suspicious.

Cheers,

Simon

On Sun, Jul 4, 2010 at 1:55 AM, Mike Matrigali <mikem_app@sbcglobal.net> wrote:
> Do any of the tables in the select have very few rows?  If so
> a few updates/inserts could change the statistics a lot.
>
> I would suggest running an offline compress on all the tables
> in the query and see if the issue still happens.  This will update
> all the statistics that the optimizer uses when estimating, including
> optimizing the indexes on all the tables.
>
> In the worst case, as a workaround you could use the optimizer hints
> to force the use of indexes to match the optimized plan if this does
> not get rid of the problem.
>
> Simon wrote:
>>

8< -------------- snip

Mime
View raw message