在创建提交时刷新 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;