Sample Header Ad - 728x90

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

0 votes
2 answers
95 views
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 !
Asked by vpi (21 rep)
May 5, 2025, 11:56 AM
Last activity: Aug 1, 2025, 06:28 PM