oracle - 使用 MAX() 在提交物化 View 上可刷新

Question

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

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

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

  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() if the query has no where clause 创建一个“REFRESHABLE ON COMMIT”物化 View .

但是无法让它工作。我试过:

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 上尝试此操作时，我收到消息物化 View 中存在 CASE 表达式。将其更改为使用 decode 修复了 10g 和 11g 上的问题。