Why does a ORA-12054 error occur when creating this simple materialized view example?

Question

ALTER TABLE RECORDINGS ADD PRIMARY KEY (ID);

CREATE MATERIALIZED VIEW LOG ON RECORDINGS TABLESPACE USERS NOLOGGING;

DROP MATERIALIZED VIEW REC_SEARCH_TEST;
CREATE MATERIALIZED VIEW REC_SEARCH_TEST
REFRESH COMPLETE ON COMMIT
AS (
    SELECT DISTINCT ID, TITLE FROM RECORDINGS
);


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

Cannot understand what is wrong here, I know that if I take out the DISTINCT clause it works, but why can I not use 'DISTINCT' if I specify 'REFRESH COMPLETE ON COMMIT' which is required.

If I use DISTINCT and REFRESH on demand there is no problem, but these are not the requirements.

Solution 1

Seems like with the addition of the DISTINCT, you've made your view's underlying SQL ineligible for fast refresh, and therefore not able to be used with ON COMMIT (even tho you specify refresh complete instead of refresh fast). From Oracle docs:

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some of the options may not be available. Table 8-4 describes the refresh modes.

Table 8-4 Refresh Modes

ON COMMIT

Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode.

ON DEMAND

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

The same document link has a list of restrictions for fast refresh as well.

Solution 2

"Perhaps the example isn't the best, because I want to expand the view to a more complicated query that will require a distinct keyword, right now I am just trying to get it working on a basic level. "

The DISTINCT is the cause of the ORA-12054.

SQL> CREATE MATERIALIZED VIEW REC_SEARCH_TEST
    REFRESH COMPLETE ON COMMIT
    AS (
       SELECT DISTINCT empno, ename FROM emp
   )
/
  2    3    4    5    6  
       SELECT DISTINCT empno, ename FROM emp
                                         *
ERROR at line 4:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view


Elapsed: 00:00:01.14
SQL> SQL> 
SQL> CREATE MATERIALIZED VIEW REC_SEARCH_TEST
    REFRESH COMPLETE ON COMMIT
    AS (
       SELECT empno, ename FROM emp
   )
/
  2    3    4    5    6  
Materialized view created.

Elapsed: 00:00:02.33
SQL> 

Why not start with a something that works? Remove the DISTINCT. Get your MView working. Then complicate it later when it becomes necessary.

Although, as you already know you cannot use a DISTINCT you will have to revise either your query's logic or your refresh strategy.

Comments

[Moz]

For example if I am running a search and I want to populate a combo box. I could keep a cache of the materialised view using "SELECT DISTINCT GENRE FROM APPS;". Why can't a set up a materialised view like this to refresh on commit? I know I can't but I'm interested in the reason and what other options are available.