Oracle - 快速刷新具有 LEFT JOINS 的物化视图更新非常慢

Question

我在 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;

物化视图本身看起来像这样（它包含 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 物化视图上添加索引，但这似乎也没有帮助。 （它仍然运行超过一分钟，这太慢了，我不会在每次更改后等待 17 个小时，所以它可能会改进更新）

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

Answer 1

我已成功在 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

不幸的是，正如你所注意到的，从 10g 包含 MIN 或 MAX 的 MV 只能在插入后提交时快速刷新（所谓仅插入 MV）。上述解决方案不适用于更新/删除（必须手动刷新 MV）。

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

Answer 2

我们最近在 Oracle 11.2.0.3 上也遇到了这个问题

。在我们的例子中，由于功能影响，不可避免地要删除“OUTER JOIN”。

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

没有任何效果，包括以下博客中提到的事情 -
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;

这阻止了 CBO 使用 HASH_SJ 提示。

为了其他人的利益而将其发布在这里。