我在Oracle中有一个包含LEFT JOIN的物化视图,需要很长时间才能更新 . 当我更新基础表时,运行需要63914.765秒(是的,这几乎是17个小时) .
我在同一个表上使用LEFT JOIN,因为我想将数据从行转移到列 . 此Oracle版本中不提供pivot命令,并且在FAST REFRESH物化视图上不允许使用GROUP BY CASE .
物化视图日志如下所示:
CREATE MATERIALIZED VIEW LOG ON Programmes_Titles
WITH PRIMARY KEY, rowid
INCLUDING NEW Values;
Materialized View本身看起来像这样(它包含700000行,Programmes_Titles表包含900000行):
CREATE MATERIALIZED VIEW Mv_Web_Programmes
REFRESH FAST ON COMMIT
AS
SELECT
t1.ProgrammeId,
t1.Title as MainTitle,
t2.Title as SecondaryTitle,
--Primary key
t1.Title_Id as t1_titleId,
t2.Title_Id as t2_titleId,
t1.rowid as t1_rowid,
t2.rowid as t2_rowid
FROM
Programmes_Titles t1,
Programmes_Titles t2
WHERE
t1.Titles_Group_Type = 'mainTitle'
AND t1.Programme_Id = t2.Programme_Id(+) AND t2.Titles_Group_Type(+) = 'secondaryTitle'
我使用的UPDATE语句是这样的:
UPDATE Programmes_Titles
SET Title = 'New title'
WHERE rowid = 'AAAL4cAAEAAAftTABB'
此UPDATE语句需要17个小时 . 使用INNER JOIN(删除()时)需要几毫秒 .
我也尝试在Mv_Web_Programmes物化视图上添加INDEXES,但这似乎也没有帮助 . (它仍然运行超过一分钟,这是缓慢的方式,我不是在每次更改后17小时等待,所以它可能会改善UPDATE)
所以我的问题是:为什么需要这么长的时间来更新基础表?我怎样才能改善这个?
2 回答
我已经设法在10.2.0.3实例上重现您的问题 . 自连接和外连接似乎是主要问题(尽管在MV的每一列上都有索引,它最终会在一分钟内更新) .
起初我以为你可以使用聚合MV:
不幸的是,正如你所注意到的那样,10g a MV that contains MIN or MAX can only be fast-refreshed on commit after insert(所谓的仅插入MV) . 上述解决方案不适用于更新/删除(MV必须手动刷新) .
您可以跟踪会话并打开跟踪文件以查看执行的SQL查询,以便您可以找到是否可以通过索引对其进行优化 .
我们最近在Oracle 11.2.0.3上也遇到过这个问题
在我们的例子中,由于功能影响而删除“外部连接”是不可避免的 .
在调查中,发现Oracle正在使用MV刷新DML添加令人讨厌的HASH_SH(Hash Semi Join)提示 .
没有任何效果,包括以下博客中提到的内容 - http://www.adellera.it/blog/2010/03/11/fast-refresh-of-join-only-mvs-_mv_refresh_use_stats-and-locking-log-stats/#comment-2975
最后,一个隐藏的提示工作......(尽管一般来说,如果可能的话应该通过改变应用来避免)
Oracle Doc ID 1949537.1建议将隐藏的_mv_refresh_use_hash_sj参数设置为FALSE应该阻止它使用该提示 .
这使用HASH_SJ提示停止了CBO .
在此处发布以符合他人的利益 .