Cannot create MATERIALIZED VIEW with REFRESH ON COMMIT on query with multiple joins

Question

In oracle, I have the following tables (ugly pseudo SQL below) :

CLIENT (CLIENT_ID NUMBER NOT NULL /*PK*/, GROUP_ID NUMBER NOT NULL)
GROUP (GROUP_ID NUMBER /*PK*/, GROUP_NAME VARCHAR2)
GROUP_DATA (GROUP_ID NUMBER /*PK*/, COUNTRY_ID VARCHAR2)

and I am trying to create the following materialized view :

CREATE MATERIALIZED VIEW MV_CLIENT
 REFRESH COMPLETE ON COMMIT
AS SELECT CLIENT.CLIENT_ID,
GROUP.GROUP_NAME,
GROUP_DATA.COUNTRY_ID
FROM CLIENT
INNER JOIN GROUP ON GROUP.GROUP_ID = CLIENT.GROUP_ID
INNER JOIN GROUP_DATA ON GROUP_DATA.GROUP_ID = CLIENT.GROUP_ID

I am getting the following error : ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

I do not understand the issue as GROUP_ID is guaranteed to exist only once in each of the GROUP and GROUP_DATA tables. I feel like tracking the updates (especially for refresh COMPLETE) should not be a problem.

Please note that creating the materialized view on CLIENT+GROUP or CLIENT+GROUP_DATA works but CLIENT+GROUP+GROUP_DATA does not. I have tried to create the MV logs and changed the COMPLETE to FAST but the error stays the same.

Now I know that a solution would be to merge GROUP and GROUP_DATA in a single table but this is not the solution I need. So my question is, is this an oracle limitation or am I missing something ? And if this is a limitation, does anyone know the rationale for this ? Thanks !

Answer 1

Yes, this is an Oracle limitation. The reason is based on the complexity of managing refreshes for multiple joined tables efficiently. To resolve the issue you could try: Switching to FAST refresh with appropriate materialized view logs on all tables, or Considering using REFRESH COMPLETE on a scheduled basis instead of ON COMMIT.

Answer 2

In the documentation (for at least 19c), Oracle recommends adding the row IDs for each table in a materialized view without aggregations. In such a case, your materialized view may be fast refreshable with the appropriate logs, although Oracle doesn't as of this version support ANSI join syntax, so your query would need to be refactored.

So your query would be:

CREATE MATERIALIZED VIEW MV_CLIENT
 REFRESH FAST ON COMMIT
AS SELECT CLIENT.CLIENT_ID, CLIENT.ROWID AS CLIENT_RID,
 GROUP.GROUP_NAME, GROUP.ROWID AS GROUP_RID,
 GROUP_DATA.COUNTRY_ID, GROUP_DATA.ROWID AS GROUP_DATA_RID
 FROM CLIENT,
 GROUP,
 GROUP_DATA
 WHERE GROUP.GROUP_ID = CLIENT.GROUP_ID
 AND GROUP_DATA.GROUP_ID = CLIENT.GROUP_ID