使用 MAX() 在提交物化视图上可刷新

Question

我受到实体属性值反模式的沉重打击。几年前的某一天，一个人认为 DDL 并不性感，并希望开发一些“足够灵活”的东西来保存有关人们的信息。他忽略了这样一个事实，即人们过去至少具有一些基本属性，如姓名、出生日期等。不仅如此，他还在该模式之上放置了一堆（充满副作用的）PL/SQL 包。这个东西成功地成为了其他应用程序所依赖的关键子系统。

快进几年，有 2000 万行。那家伙已经不在公司了，我必须处理这件事。我需要实现一些基本搜索，现在需要多个内部联接，并且在某些情况下需要永远进行。重写整个事情是不可能的，所以我想“枢轴”最重要的属性。

我认为物化视图可能是一个可行的选择，但我需要一些指导，因为我从未使用过它们。我想要一个这样的表：

  select
      uid,
       max(case when att = 'NAME' then UPPER(value) end) name,
       max(case when att = 'SURNAME' then UPPER(value) end) surname,
       max(case when att = 'BIRTH' then DATEORNULL(value) end) birth,
     ....,
     count(*) cnt
 from t
 group by uid

据我了解，阅读 Oracle 文档，我应该能够使用 MAX() 创建一个“REFRESHABLE ON COMMIT”物化视图 如果查询没有where子句。

但无法让它发挥作用。我已经尝试过：

create materialized view log on t WITH SEQUENCE,ROWID,(value) INCLUDING NEW VALUES;

create materialized view t_view
 refresh fast on commit
 as
  select
      uid,
       max(case when att = 'NAME' then UPPER(value) end) name,
       max(case when att = 'SURNAME' then UPPER(value) end) surname,
       max(case when att = 'BIRTH' then DATEORNULL(value) end) birth,
     count(*) cnt
 from t
 group by uid

它适用于插入，但不适用于更新。我看到它能够完成这些事情：

  REFRESH_COMPLETE                                                             

  REFRESH_FAST                                                                 

  REFRESH_FAST_AFTER_INSERT

但我认为我还应该看到 REFRESH_FAST_AFTER_ONETAB_DML。有什么想法吗？

更新：dbms_mview.explain_mview 的输出

REFRESH_COMPLETE             |Y|
REFRESH_FAST                 |Y|
REFRESH_FAST_AFTER_INSERT    |Y|
REFRESH_FAST_AFTER_ONETAB_DML|N|mv uses the MIN or MAX aggregate functions
REFRESH_FAST_AFTER_ANY_DML   |N|see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT             |N|PCT is not possible on any of the detail tables in the mater

Answer 1

MV_CAPABILITIES_TABLE.MSGTXT 是错误的，您真正需要做的是将 case 替换为 decode。

当我在 11g 上尝试此操作时，我收到消息物化视图中存在 CASE 表达式。将其更改为使用 decode 修复了 10g 和 11g 上的问题。