The fundamental issue here is that in this poor performing case, derby is not looking at the index on the very large table that would immediately reduce the dataset. For whatever reason the optimizer is making a the worst possible case decision.
Two thoughts:
1) Have you verified that the statistics are accurate for that table and index? Derby only updates the statistics periodically, and under certain circumstances. You can call SYSCS_UTIL.SYSCS_COMPRESS_TABLE to force a re-update of the statistics, to see if that helps. 2) Have you investigated the Derby optimizer overrides feature? http://db.apache.org/derby/docs/10.2/tuning/ctunoptimzoverride.html thanks, bryan
