实例化视图使用HAVING子句快速刷新? [英] Materialized view fast refresh with HAVING clause?

问题描述

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

On Oracle 11g I'm trying to create a materialized view with FAST REFRESH ON COMMIT that contains a HAVING clause.

《数据库数据仓库指南》 说:

快速刷新的一般限制

General Restrictions on Fast Refresh

实例化视图的定义查询受到如下限制:

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

• 它不能包含带有子查询的HAVING子句.

但是，如果我将HAVING count(*)>1(注意:没有子查询)添加到本来可以工作的物化视图中，则会出现此错误:

But if I add HAVING count(*)>1 (note: no subquery) to an otherwise working materialized view, I get this error:

ORA-12054:无法为实例化视图设置ON COMMIT刷新属性

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

注意:已创建实例化视图日志(否则，即使第一个示例也不起作用).

Note: The materialized view logs are created (otherwise not even the first example would work).

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

Why doesn't it work? Does anyone know a MV example with a HAVING clause? So at least I could start from there (I googled but I found none).

注2:我想要HAVING的原因是将视图中的行数从数千甚至上百万减少到几.为了节省存储空间(并可能获得性能).

Note2: The reason I want the HAVING is to reduce the number of rows in the view from thousands or even million to just a few. To save storage (and possibly gain performance).

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

PS: Exact Oracle database version used: 11.2.0.3.0

推荐答案

是的，文档似乎并不准确.

Yes, the documentation does not seem to be accurate.

作为解决方法，您可以尝试实现嵌套的实例化视图.

As a workaround you can try implementing nested materialized views.

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

这篇关于实例化视图使用HAVING子句快速刷新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！