创建物化视图比创建表慢得多
我有一个查询，我想用它来支持物化视图。
当我使用此语法从视图创建常规表时，它执行得非常快（5 秒）：
create table my_table as select * from my_view;
但是，当我尝试从同一视图创建物化视图时，它要慢得多（5 分钟以上）。
create materialized view my_materialized_view refresh complete start with sysdate next trunc(sysdate, 'HH') + (1/24) AS select * from my_view;
我已经尽可能地优化了视图查询。直接从视图中选择非常快，也可以从视图中创建任意数量的表。只有在我通过创建物化视图之后才可以。
有什么想法可能导致速度放缓吗？在创建物化视图时，我可以做些什么（提示等）来帮助它更快地执行？
I've got a query that I'd like to use to back a materialized view.
When I use this syntax to create a regular table from the view, it executes very quickly (5 seconds):
create table my_table as select * from my_view;
However, when I try to create the materialized view from the same view, it's MUCH slower (5+ minutes).
create materialized view my_materialized_view refresh complete start with sysdate next trunc(sysdate, 'HH') + (1/24) AS select * from my_view;
I've already optimized the view query as much as possible. Selecting directly from the view is quick, as well as creating any number of tables from the view. It's only after I pass it through create materialized view.
Any ideas what could be causing the slowdown? Anything I can do (hints, etc) to help it execute quicker when creating the materialize view?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
抱歉，我没有答案。我也遇到过这个问题，无法解释。就我而言，我只是将 MV 实现为一个表格，每个人都很高兴。
Sorry that I don't have an answer. I too have encountered this problem and could not explain it. In my case I just implemented the MV as a table, and everyone was happy.
我认为差异（和问题）可能是刷新确实删除并插入。创建表只执行选择插入操作。但刷新会删除-选择-插入。
尝试用截断来做到这一点。不记得语法，可以在文档中找到。
I think the difference(and the problem) may be that the refresh does delete and insert. The create table does only select-insert. But refresh does delete-select-insert.
Try to do it with truncate. Don't know the sintax by heart, can be found in docs.