一组类似 Oracle 函数的优化选项
我有三个自定义函数，它们执行非常相似的操作：它们从相同的、相当复杂的连接集中提取不同的数据 - 生成连接表需要时间 - 并且通常都在同一个 select< 中调用/代码>。这显然效率低下，我想提高性能，但是最好的方法是什么？
- 创建复杂连接的物化视图，涵盖所有参数，并在每个函数中引用它（或者完全省略函数）。
- 将三个函数组合在一起并以自定义类型一次返回所有值。
- 还有别的事吗？
对于像我这样的新手来说，第一个选项似乎可能是最好的解决方案；但它显然有一个缺点，即创建一个相当大的物化视图，这需要维护（因此它会根据需要刷新）；虽然这个 MV 可能在其他地方有用......第二个选项有点黑客；但还有什么是我没有考虑到的吗？
I have three custom functions that do very similar things: they pull different data from the same, rather complicated set of joins -- generating the joined table is what takes the time -- and are usually all called in the same select. This is obviously inefficient and I would like to improve performance, but what is the best way to go about it?
- Create a materialised view of the complex join, covering all parameters, and just refer to this in each of the functions (or just omit the functions, altogether).
- Roll the three functions together and return all values at once in a custom type.
- Something else?
The first option seems, to a rookie like me, probably the best solution; but it obviously has the drawback of creating a pretty large materialised view, which would need maintenance (so it's refreshed as required); although this MV might be useful elsewhere... The second option would be a bit of a hack; but is there anything else that I haven't considered?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我宁愿完全省略这些函数并重新编写查询，以便不使用函数。任何选择数据的函数（特别是使用“复杂连接”）都是减慢查询速度的肯定方法，因为该函数必须为主查询处理的每一行（甚至不一定返回）执行一次，可能是 1000 秒（或100,000s）次。
I would prefer to omit the functions altogether and re-write the queries so that functions are not used. Any function that selects data (especially using a "complex join") is a sure way to slow down your query, since the function must be executed once for each row processed (not even necessarily returned) by the main query, maybe 1000s (or 100,000s) of times.
老实说，我会选择选项 2，除非你真的想要一个物化视图。您可以将该函数转换为表格，以便您可以正常地从中进行选择。
I would choose option 2 to be honest, unless you really want a materialised view. You can cast the function as a table so you can select from it as normal.