sql - 物化 View 使用 HAVING 子句快速刷新？

Question

在 Oracle 11g 上，我尝试使用包含 HAVING 子句的 FAST REFRESH ON COMMIT 创建一个物化 View 。

Database Data Warehousing Guide说:

General Restrictions on Fast Refresh

The defining query of the materialized view is restricted as follows:

• It cannot contain a HAVING clause with a subquery.

但是，如果我将 HAVING count(*)>1 (注意:没有子查询)添加到其他工作的物化 View 中，则会收到此错误:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

dbms_mview.explain_mview() 说:

REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N   2011 a HAVING clause is present

实际命令:

SQL>  create materialized view mv1 refresh fast on commit as
  2      select UserId, count(*) from USERS group by UserId;

Materialized view created.

SQL> DROP MATERIALIZED VIEW mv1;

Materialized view dropped.

SQL> create materialized view mv1 refresh fast on commit as
  2      select UserId, count(*) from USERS group by UserId
  3          having count(*)>1; -- the only difference
    having count(*)>1
                    *
ERROR at line 5:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

注意:物化 View 日志已创建(否则第一个示例将无法工作)。

为什么不起作用？有谁知道带有 HAVING 子句的 MV 示例吗？所以至少我可以从那里开始(我用谷歌搜索但没有找到)。

注2:我想要HAVING的原因是为了将 View 中的行数从数千甚至数百万减少到几行。节省存储空间(并可能提高性能)。

PS:使用的确切 Oracle 数据库版本:11.2.0.3.0

Answer 1

是的，文档似乎不准确。

作为解决方法，您可以尝试实现嵌套物化 View 。

CREATE MATERIALIZED VIEW mv1 
REFRESH FAST ON COMMIT 
AS
SELECT col1,
       COUNT(col1) count_col1
FROM test_table
GROUP BY col1

ALTER MATERIALIZED VIEW mv1 ADD CONSTRAINT pk_mv1 PRIMARY KEY (col1)

CREATE MATERIALIZED VIEW LOG ON mv1 WITH PRIMARY KEY;

CREATE MATERIALIZED VIEW MV2 
REFRESH FAST ON COMMIT AS
SELECT col1,
       count_col1
FROM   mv1
WHERE  count_col1 > 1