Materialized view fast refresh with HAVING clause?

Question

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

The Database Data Warehousing Guide says:

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.

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

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

dbms_mview.explain_mview() says:

REFRESH_FAST                   N
REFRESH_FAST_AFTER_INSERT      N   2011 a HAVING clause is present

The actual commands:

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).

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).

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: Exact Oracle database version used: 11.2.0.3.0

Answer 1

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