oracle 为什么在创建这个简单的物化视图示例时会出现 ORA-12054 错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8883662/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does a ORA-12054 error occur when creating this simple materialized view example?
提问by Moz
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
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 COMPLETE ON COMMIT”，为什么我不能使用“DISTINCT”。
If I use DISTINCT and REFRESH on demand there is no problem, but these are not the requirements.
如果我按需使用 DISTINCT 和 REFRESH 没有问题，但这些不是要求。
采纳答案by tbone
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:
似乎通过添加 DISTINCT，您使视图的基础 SQL 不符合快速刷新的条件，因此无法与 ON COMMIT 一起使用（即使您指定刷新完成而不是快速刷新）。来自Oracle 文档：
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.
两种刷新执行模式是 ON COMMIT 和 ON DEMAND。根据您创建的实体化视图，某些选项可能不可用。表8-4 描述了刷新模式。
Table 8-4 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.
当修改实体化视图的详细表之一的事务提交时，刷新会自动发生。只要物化视图可以快速刷新（换句话说，不复杂），就可以指定这一点。使用此模式需要 ON COMMIT 权限。
ON DEMAND
一经请求
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).
当用户手动执行 DBMS_MVIEW 包（REFRESH、REFRESH_ALL_MVIEWS、REFRESH_DEPENDENT）中包含的可用刷新过程之一时，就会发生刷新。
The same document link has a list of restrictions for fast refresh as well.
同一个文档链接也有快速刷新的限制列表。
回答by APC
"Perhaps the example isn't the best, because I want to expand the view to a more complicated query that will require a distinct keyword, right now I am just trying to get it working on a basic level. "
“也许这个例子不是最好的，因为我想将视图扩展到一个需要不同关键字的更复杂的查询，现在我只是试图让它在基本级别上工作。”
The DISTINCT is the cause of the ORA-12054.
DISTINCT 是 ORA-12054 的原因。
SQL> CREATE MATERIALIZED VIEW REC_SEARCH_TEST
REFRESH COMPLETE ON COMMIT
AS (
SELECT DISTINCT empno, ename FROM emp
)
/
2 3 4 5 6
SELECT DISTINCT empno, ename FROM emp
*
ERROR at line 4:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
Elapsed: 00:00:01.14
SQL> SQL>
SQL> CREATE MATERIALIZED VIEW REC_SEARCH_TEST
REFRESH COMPLETE ON COMMIT
AS (
SELECT empno, ename FROM emp
)
/
2 3 4 5 6
Materialized view created.
Elapsed: 00:00:02.33
SQL>
Why not start with a something that works? Remove the DISTINCT. Get your MView working. Then complicate it later when it becomes necessary.
为什么不从一个有用的东西开始呢？删除 DISTINCT。让您的 MView 工作。然后在以后需要时将其复杂化。
Although, as you already know you cannot use a DISTINCT you will have to revise either your query's logic or your refresh strategy.
尽管您已经知道不能使用 DISTINCT，但您必须修改查询的逻辑或刷新策略。
回答by Karsten Spang
The important thing to note about the question is that it is notabout fast refresh, but complete refresh. Thus, there is no logical reason that the usual restrictions for fast refresh on commit should apply, except the one that all referenced objects must be local.
关于这个问题需要注意的重要一点是，它不是关于快速刷新，而是关于完全刷新。因此，除了所有引用的对象都必须是本地的之外，没有任何逻辑上的理由应该应用对提交时快速刷新的通常限制。
The "refresh complete on commit is a relatively new feature, so the best answer to the "why" question is probably "Oracle has not yet implemented this fully, please check future versions of Oracle Database". Not very useful, but true...
“提交时刷新完成是一个相对较新的功能，因此对“为什么”问题的最佳答案可能是“Oracle 尚未完全实现此功能，请检查 Oracle 数据库的未来版本”。不是很有用，但确实如此。 .
