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

Answer 1

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