Derby的子查询优化问题
这是我关于 Derby 的上一个问题。我终于得到它来向我展示查询的执行计划，并且出现了有趣的值： http://pastebin.com/wQAicPAV （所以解析器变得疯狂，所以我需要使用外部存储）
当你可以看到，一些优化器的成本非常大，并且还要估计行数。 优化器估计行数：333734973.00 优化器估计成本：205150563.85 完全错误的价值观。该查询需要 22 分钟才能执行。
我见过这个问题： https://issues.apache.org/jira/browse/DERBY-1905
这表明大多数时候优化器只是试图找到该查询的最佳计划，而执行本身的持续时间要少得多。
所以我的问题是——如何避免这种情况？配置德比可能是？优化器提示？为那个贪婪的混蛋优化器设置超时？我找不到任何关于它的参考资料，而且我根本没有德比的经验。
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
Which shows that most of the time optimizer is just trying to find the best plan for this query, and execution itself lasts significantly less.
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
根据我收集的信息，您只能通过重写查询来避免这种情况，因为优化器对于此类查询“表现不佳” - 有时它有助于使用 JOIN 和/或从查询的某些部分创建视图并在查询中使用这些视图。
一些一般性能建议（不确定您是否已经尝试过）：
From what I gather you can only avoid this by rewriting the query since the optimizer is "behaving badly" with this sort of query - sometimes it helps to use JOINs and or create views from some parts the query and use those in the query.
Some general performance advice (not sure you already tried that):