在创建提交时刷新 MV 时，我可以让它与一个连接一起工作，但与两个连接一起工作时会失败。我想这与密钥保留有关，但我看不出是怎么回事。我试过 DBMS_MVIEW.EXPLAIN_M...

在创建提交时刷新 MV 时，我可以让它与一个连接一起工作，但与两个连接一起工作时会失败。我想这与密钥保留有关，但我看不出是怎么回事。我试过了， DBMS_MVIEW.EXPLAIN_MVIEW 但它给了我 ORA-32342: The EXPLAIN_MVIEW facility failed to explain the materialized view statement

CREATE TABLE TMP_20240730_171526_B
(
    ID INT PRIMARY KEY
);
CREATE TABLE TMP_20240730_171526_C
(
    ID INT PRIMARY KEY
);
CREATE TABLE TMP_20240730_171526_A
(
    ID   INT PRIMARY KEY,
    B_ID INT
        CONSTRAINT TMP_20240730_171526_A_B_FK REFERENCES TMP_20240730_171526_B,
    C_ID INT
        CONSTRAINT TMP_20240730_171526_A_C_FK REFERENCES TMP_20240730_171526_C
);


-- this works
CREATE MATERIALIZED VIEW TMP_20240730_171749_MV REFRESH COMPLETE ON COMMIT
AS
SELECT A.ID A_ID, B.ID B_ID
FROM TMP_20240730_171526_A A
         JOIN TMP_20240730_171526_B B ON A.B_ID = B.ID;
DROP MATERIALIZED VIEW TMP_20240730_171749_MV;

-- this fails: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
CREATE MATERIALIZED VIEW TMP_20240730_171749_MV REFRESH COMPLETE ON COMMIT
AS
SELECT A.ID A_ID, B.ID B_ID
FROM TMP_20240730_171526_A A
         JOIN TMP_20240730_171526_B B ON A.B_ID = B.ID
         JOIN TMP_20240730_171526_C C ON A.C_ID = C.ID
;

-- clean up
-- DROP MATERIALIZED VIEW TMP_20240730_171749_MV;
DROP TABLE TMP_20240730_171526_A;
DROP TABLE TMP_20240730_171526_B;
DROP TABLE TMP_20240730_171526_C;