Can't set the ON COMMIT refresh attribute when creating a materialized view containing partial primary key in Oracle

Question

I need to extract the unique values of a column which is part of the primary key from a table into a materialized view. I can create the materialized view if using "refresh complete" but with no luck when trying to use "refresh fast on commit". Can anyone point out whether I missed anything or Oracle does not support such action.

The example output is listed below. Thanks.

SQL> create table TEST( col1 number, col2 number, col3 varchar(32), CONSTRAINT test_pk Primary Key (col1, col2));

Table created.

SQL> create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test;
create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test
                                                                                                     *
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


SQL> create materialized view test_mv build immediate refresh complete as select distinct col2 from test;

Materialized view created.

SQL> drop materialized view test_mv;

Materialized view dropped.

SQL> create materialized view log on test;

Materialized view log created.

SQL> create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test;
create materialized view test_mv build immediate refresh fast on commit as select distinct col2 from test
                                                                                                     *
ERROR at line 1:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

Husqvik · Answer 1 · 2015-10-23T04:23:17.707

Main issue of your view is the DISTINCT clause. On commit fast refresh is super sensitive to underlying query. There exist many rules that must be fulfilled for a materialized view to support fast refresh. DISTINCT prevents it.

You can check the capabilities of a materialized view using DBMS_MVIEW.EXPLAIN_MVIEW procedure:

DECLARE
    result SYS.EXPLAINMVARRAYTYPE := SYS.EXPLAINMVARRAYTYPE();
BEGIN
    DBMS_MVIEW.EXPLAIN_MVIEW('TEST_MV', result);

    FOR i IN result.FIRST..result.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(result(i).CAPABILITY_NAME || ': ' || CASE WHEN result(i).POSSIBLE = 'T' THEN 'Yes' ELSE 'No' || CASE WHEN result(i).RELATED_TEXT IS NOT NULL THEN ' because of ' || result(i).RELATED_TEXT END || '; ' || result(i).MSGTXT END);
    END LOOP;
END;

You find more information in documentation http://docs.oracle.com/cd/B28359_01/server.111/b28313/basicmv.htm#i1007007

You're right that distinct prevents it. But, oddly, you can create one with a `GROUP BY` and Oracle will be able to use that for `DISTINCT` queries. See my answer for details. — Jon Heller, Oct 24 '15 at 03:16
I was looking for a query to execute `EXPLAIN_MVIEW`, and yours helped me a lot. Respect +1. — coding monster, Feb 28 '22 at 15:59

Jon Heller · Answer 2 · 2015-10-29T02:40:43.433

Fast refresh views are picky. This solution requires a materialized view log with specific properties, and a materialized view with a few extra features and a different syntax.

DISTINCT alone does not appear to be supported. But there are aggregate materialized views that support GROUP BY. If that materialized view is created with ENABLE QUERY REWRITE, Oracle can use it in a DISTINCT query. There is also an extra COUNT(*) because "COUNT(*) must always be present to guarantee all types of fast refresh."

Create table, materialized view log, and materialized view.

create table test(col1 number, col2 number, col3 varchar(32)
  ,constraint test_pk primary key (col1, col2));
create materialized view log on test with rowid (col2) including new values;
create materialized view test_mv
  build immediate
  refresh fast on commit
  enable query rewrite as
  select col2, count(*) total from test group by col2;

Queries can use the materialized view.

These explain plans show that the materialized view works for both a GROUP BY and a DISTINCT query.

explain plan for select col2 from test group by col2;
select * from table(dbms_xplan.display);

explain plan for select distinct col2 from test;
select * from table(dbms_xplan.display);


Plan hash value: 1627509066

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| TEST_MV |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

As I am using Oracle SE, I don't have query rewrite support but I can still create the materialized view without the "enable query rewrite" clause. The drawback is that it will result in full table access when run the two queries in your answer. Anyway, I don't worry about this right now and believe once I move to Oracle EE, the problem will disappear. What I am confused now is after updating col2 in the base table test, the mv does not get updated/refreshed. The mv will get updated after inserting new records in the base table. Is there any other thing I missed/misunderstood? — Jason, Oct 29 '15 at 01:34
It's something I missed - aggregate materialized views should include a `COUNT(*)`. The modified example works better. — Jon Heller, Oct 29 '15 at 02:43

Can't set the ON COMMIT refresh attribute when creating a materialized view containing partial primary key in Oracle

2 Answers2