here is to follow my previous question on Derby. I finally got it to show me the execution plan for my query and intresting values came up: http://pastebin.com/wQAicPAV (SO parser goes crazy so i need to use external storage)
As you can see, some optimizer costs are INCREDIBLY BIG, and estimate row count too. optimizer estimated row count: 333734973.00 optimizer estimated cost: 205150563.85 Completely wrong values. And the query takes 22 minutes to execute.
I ve seen this issue: https://issues.apache.org/jira/browse/DERBY-1905
这表明,大多数时间最优化者只是试图为这一询问找到最佳计划,而执行本身则大大低于预期。
So my question is - how to avoid this situation? Configure derby may be? Optimizer hints? Set timeout for that greedy basterd optimizer? I cant find anything about it in reference and i have no experience with derby at all.