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
Last activity: Aug 1, 2025, 06:28 PM