db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Synge" <james.sy...@gmail.com>
Subject Re: Diagnosing DERBY-47 and DERBY-713 (queries with IN (?,?) filters)
Date Sun, 08 Oct 2006 17:16:58 GMT
On 10/2/06, Yip Ng <yipng168@gmail.com> wrote:
> One way is to dump the actual generated code into the file system.  You can do this
> using Java property -Dderby.debug.true=DumpClassFile.  e.g.:
>
> java -Dderby.debug.true=DumpClassFile  org.apache.derby.tools.ij

Thanks for that tip.

Thanks also to Army for his detailed reply.

I've done some further debugging, this time through the execution of the query,
and have a much better understand of how bad the generated plan is for this kind
of query.  I've updated the wiki with my findings:

http://wiki.apache.org/db-derby/DerbyBug47

In summary, two problems are:

1)
    The IN list is used to create a scan of the index, starting with the lowest
    value in the IN list, and continuing through the greatest value in
the IN list
    (as opposed to creating N probes into the BTree).

2)
    After finding an index row in the range, rather than testing the predicate
    against the index row, the base table row is fetched before the predicate
    (restriction) is applied.

James

Mime
View raw message