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

@GordonLinoff - the functionality is documented not wo work with a HAVING clause with a subquery. The question makes perfect sense - the OP claims he has no subqueries in his MV definition. Whether he does or not is another question - he posted something different from his actual MV definition - but the question makes perfect sense. — mathguy
@mathguy The examples in the question reproduce the problem and they have no subqueries.I'll remove the mention of "other" query in question to avoid confusion. (allthough then someone will say the simple posted example makes no business sense...). — David Balažic
Well, in the meantime Gordon withdrew his question/objection to your post, so it's a moot point. I just recently installed the Enterprise version on one of my computers (I am an enthusiast just learning for myself) - your question is giving the motivation to, today, try exactly what you described and confirm it. It is known that sometimes even the documentation is wrong, just keep that in mind. — mathguy

phonetic_man phonetic_man · Accepted Answer · 2016-08-05T05:08:46

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

Materialized view fast refresh with HAVING clause?

1 Answers