为什么在创建这个简单的物化视图示例时会出现 ORA-12054 错误?

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".

Cannot understand what is wrong here, I know that if I take out the DISTINCT clause it works, but why can I not use 'DISTINCT' if I specify 'REFRESH COMPLETE ON COMMIT' which is required.

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

If I use DISTINCT and REFRESH on demand there is no problem, but these are not the requirements.

推荐答案

似乎通过添加 DISTINCT，您使视图的底层 SQL 不符合快速刷新的条件，因此无法与 ON COMMIT (即使您指定刷新完成而不是快速刷新).来自 Oracle 文档:

Seems like with the addition of the DISTINCT, you've made your view's underlying SQL ineligible for fast refresh, and therefore not able to be used with ON COMMIT (even tho you specify refresh complete instead of refresh fast). From Oracle docs:

两种刷新执行模式是ON COMMIT 和ON DEMAND.依赖在您创建的物化视图上，某些选项可能不可用的.表 8-4 描述了刷新模式.

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 刷新模式

提交

当一个事务修改了其中之一时自动发生刷新物化视图的明细表提交.这个可以指定只要物化视图可以快速刷新(换句话说，不复杂).使用此模式需要 ON COMMIT 权限.

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.

按需

当用户手动执行可用的其中之一时会发生刷新DBMS_MVIEW 包 (REFRESH,REFRESH_ALL_MVIEWS，REFRESH_DEPENDENT).

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).

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

The same document link has a list of restrictions for fast refresh as well.