oracle 物化视图创建速度很快，但刷新需要几个小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14983941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
materialized view creation is fast but refresh takes hours
提问by Yili Li
I am using a materialized view, and I cant set it to fast refresh because some of the tables are from remote database which does not have materialized view log.
我正在使用物化视图，我无法将其设置为快速刷新，因为某些表来自没有物化视图日志的远程数据库。
When I create the materialized view, it took like 20 30 seconds. however when I was trying to refresh it. It took more than 2 3 hours. and total number of records are only around 460,000.
当我创建物化视图时，花了大约 20 30 秒。但是当我试图刷新它时。花了2个3小时多。总记录数只有46万左右。
Does anyone have any clue about how it would happen?
有没有人知道它会如何发生？
Thanks
谢谢
Code looks like as following
代码如下所示
create materialized view MY_MV1
refresh force on demand
start with to_date('20-02-2013 22:00:00', 'dd-mm-yyyy hh24:mi:ss') next trunc(sysdate)+1+22/24
as
( SELECT Nvl(Cr.Sol_Chng_Num, ' ') AS Change_Request_Nbr,
Nvl(Sr.Sr_Num, ' ') AS Service_Request_Nbr,
Nvl(Sr.w_Org_Id, 0) AS Org_Id,
Fcr.rowid,
Cr.rowid,
Bsr.rowid,
Sr.rowid,
SYSDATE
FROM [email protected] Fcr
INNER JOIN [email protected] Cr
ON Fcr.w_Sol_Chng_Id = Cr.w_Sol_Chng_Id
INNER JOIN [email protected] Bsr
ON Fcr.w_Sol_Chng_Id = Bsr.w_Sol_Chng_Id
INNER JOIN [email protected] Sr
ON Sr.w_Srv_Rec_Id = Bsr.w_Srv_Rec_Id
WHERE Sr.Sr_Num <> 'NS'
);
I have tried to use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) but it still took 141 mins to run... vs 159 mins without atomic_refresh=>false
我曾尝试使用 dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false) 但它仍然需要 141 分钟才能运行...... vs 159 分钟没有 atomic_refresh=>false
回答by tbone
I would personally NOT use the scheduler built into the mat view CREATE statement (start with ... next clause).
我个人不会使用内置于 mat view CREATE 语句中的调度程序（以 ... next 子句开头）。
The main reason (for me) is that you cannot declare the refresh non-ATOMIC this way (at least I haven't found the syntax for this at CREATE time). Depending on your refresh requirements and size, this can save A LOT of time.
主要原因（对我而言）是您不能以这种方式声明刷新非 ATOMIC（至少我在 CREATE 时还没有找到此语法）。根据您的刷新要求和大小，这可以节省大量时间。
I would use dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false). This would:
我会使用 dbms_mview.refresh('MY_MATVIEW', 'C', atomic_refresh=>false)。这个会：
- Truncate MY_MATVIEW snapshot table
- Insert append into MY_MATVIEW table
- 截断 MY_MATVIEW 快照表
- 插入追加到 MY_MATVIEW 表
If you use the next clause in the create statement, it will setup an atomic refresh, meaning it will:
如果在 create 语句中使用 next 子句，它将设置原子刷新，这意味着它将：
- Delete * from MY_MATVIEW
- Insert into MY_MATVIEW
- Commit
- 从 MY_MATVIEW 中删除 *
- 插入 MY_MATVIEW
- 犯罪
This will be slower (sometimes much slower), but others can still query from MY_MATVIEW while the refresh is occurring. So, depends on your situation and needs.
这会更慢（有时会慢得多），但其他人仍然可以在刷新时从 MY_MATVIEW 查询。所以，看你的情况和需求。
回答by Anonymous
You can test it. I run this manually and it works for me friend :)
你可以测试一下。我手动运行它，它对我的朋友有用:)
BEGIN
DBMS_REFRESH.make(
name => 'DB_NAME.MINUTE_REFRESH',
list => '',
next_date => SYSDATE,
interval => '/*1:Mins*/ SYSDATE + 1/(60*24)',
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/
BEGIN
DBMS_REFRESH.add(
name => 'DB_NAME.MINUTE_REFRESH',
list => 'DB_NAME.MV_NAME',
lax => TRUE);
END; /
结尾; /
And then u can destroy it with this.
然后你可以用这个摧毁它。
BEGIN
DBMS_REFRESH.destroy(name => 'DB_NAME.MINUTE_REFRESH');
END;
/
You can create materialize view log.
您可以创建物化视图日志。
CREATE MATERIALIZED VIEW LOG ON DB_NAME.TABLE_NAME
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;
I hope it can help you. :)
我希望它能帮助你。:)
回答by northpole
if it only takes 20-30 seconds to create why not just drop and recreate the materialized view instead of refreshing it?
如果创建只需要 20-30 秒，为什么不直接删除并重新创建物化视图而不是刷新它？
