On 10/2/06, Yip Ng <[EMAIL PROTECTED]> 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
