Oracle - 快速刷新带有左连接的物化视图更新非常慢

问题描述：

我在Oracle中有一个包含LEFT JOIN的物化视图，需要很长时间才能更新。当我更新底层表时，需要运行63914.765秒（是的，差不多17小时）。Oracle - 快速刷新带有左连接的物化视图更新非常慢

我在同一张表上使用了LEFT JOIN，因为我想将数据从行转移到列。在此Oracle版本中，pivot命令不可用，并且在FAST REFRESH物化视图上不允许使用GROUP BY + CASE。

物化视图日志看起来是这样的：

CREATE MATERIALIZED VIEW LOG ON Programmes_Titles 
WITH PRIMARY KEY, rowid 
INCLUDING NEW Values;

物化视图本身看起来像这样（包含70万行，在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语句ta kes 17个小时。使用INNER JOIN（删除（+））时，需要几毫秒。

我也尝试在Mv_Web_Programmes物化视图中添加INDEXES，但这似乎也没有帮助。（它仍然运行超过一分钟，这是缓慢的方式，我不等待17小时后，每次更改，所以它可能会改善更新）

所以我的问题是：为什么需要这么长时间更新底层表？我该如何改进？

答

我已经设法在10.2.0.3实例上重现您的问题。自连接和外连接似乎是主要问题（尽管MV中每一列的索引都会在一分钟内完成更新）。

起初我还以为你可以使用聚合MV：

SQL> CREATE MATERIALIZED VIEW LOG ON Programmes_Titles 
    2 WITH PRIMARY KEY, ROWID (programmeId, Titles_Group_Type, title) 
    3 INCLUDING NEW Values; 

Materialized view log created 

SQL> CREATE MATERIALIZED VIEW Mv_Web_Programmes 
    2 REFRESH FAST ON COMMIT 
    3 AS 
    4 SELECT ProgrammeId, 
    5   MAX(decode(t1.Titles_Group_Type, 'mainTitle', t1.Title)) MainTl, 
    6   MAX(decode(t1.Titles_Group_Type, 'secondaryTitle', t1.Title)) SecTl 
    7 FROM Programmes_Titles t1 
    8 GROUP BY ProgrammeId; 

Materialized view created

不幸的是，因为你已经注意到，随着万兆a MV that contains MIN or MAX can only be fast-refreshed on commit after insert的（所谓的唯一刀片式MV）。上述解决方案不适用于更新/删除（MV将不得不手动刷新）。

您可以跟踪会话并打开跟踪文件以查看执行了哪些SQL查询，以便您可以查找是否可以通过索引对其进行优化。

谢谢，建议的解决方案工作完美。还有一个问题。在我的示例查询中，我只在JOIN中使用单个条件，但我真正的查询使用了两个。我仍然可以使用DECODE功能（这对我来说是新的，还是应该使用CASE？） – Tejo

对不起，我有点快。 Programme_Titles表在一秒钟内获得新值，但Mv_Web_Programmes不更新，旧值仍然存在。任何想法？ – Tejo

只要您在CASE语句中使用的每一列都在MV日志中，您就应该可以使用CASE而不是DECODE。 –

答

我们也面临这个问题最近在Oracle 11.2.0.3

在我们的情况下，这是不可避免的要删除一个“OUTER JOIN”由于功能的影响。

经调查发现，Oracle正在用MV刷新DML添加令人讨厌的HASH_SH（哈希半连接）提示。

毫无效果包括以下blog- 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应该防止使用该提示。

alter session set "_mv_refresh_use_hash_sj"=FALSE;

使用HASH_SJ提示停止了CBO。

张贴在这里是为了他人的利益。

Oracle - 快速刷新带有左连接的物化视图更新非常慢

相关推荐