oracle - 为什么在创建这个简单的实体化 View 示例时会出现 ORA-12054 错误？

Question

ALTER TABLE RECORDINGS ADD PRIMARY KEY (ID);

CREATE MATERIALIZED VIEW LOG ON RECORDINGS TABLESPACE USERS NOLOGGING;

DROP MATERIALIZED VIEW REC_SEARCH_TEST;
CREATE MATERIALIZED VIEW REC_SEARCH_TEST
REFRESH COMPLETE ON COMMIT
AS (
    SELECT DISTINCT ID, TITLE FROM RECORDINGS
);


ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

无法理解这里出了什么问题，我知道如果我去掉 DISTINCT 子句它会起作用，但是如果我指定了必需的“REFRESH COMPLETE ON COMMIT”，为什么我不能使用“DISTINCT”。

如果我按需使用 DISTINCT 和 REFRESH 没有问题，但这些不是要求。

Answer 1

似乎通过添加 DISTINCT，您已经使 View 的底层 SQL 不符合快速刷新的条件，因此无法与 ON COMMIT 一起使用(即使您指定刷新完成而不是快速刷新)。来自 Oracle docs :

The two refresh execution modes are ON COMMIT and ON DEMAND. Depending on the materialized view you create, some of the options may not be available. Table 8-4 describes the refresh modes.

表 8-4 刷新模式

提交

Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode.

点播

Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT).

同一个文档链接也有一个快速刷新限制列表。