oracle ORA-01446 - 无法从带有 DISTINCT、GROUP BY 等的视图中选择 ROWID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9806657/
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
ORA-01446 - cannot select ROWID from view with DISTINCT, GROUP BY, etc
提问by Miguel Ribeiro
I created a view that has a distinct in the select clause.
我创建了一个在 select 子句中有一个不同的视图。
When I try to select all the records with "select * from view" I get the following error:
当我尝试使用“select * from view”选择所有记录时，出现以下错误：
ORA-01446: cannot select ROWID from view with DISTINCT, GROUP BY, etc.
I was searching why this happens and came to this:
我正在寻找为什么会发生这种情况并得出以下结论：
You tried to create a view that included a ROWID in the SELECT statement as well as a clause such as DISTINCT or GROUP BY. This is not allowed. Reference
您试图创建一个视图，该视图在 SELECT 语句中包含一个 ROWID 以及一个子句，例如 DISTINCT 或 GROUP BY。这是不允许的。参考
This is awkward because the select stament in the view does not selects the rowid and it isn't being used in any other clause (where, order, etc..)
这很尴尬，因为视图中的 select 语句没有选择 rowid 并且它没有用于任何其他子句（where、order 等）。
Any idea on this?
对此有什么想法吗？
Update
更新
I'm not able to post the query itself but i'm posting a look-a-like. Here it is:
我无法发布查询本身，但我发布了一个类似的内容。这里是：
SELECT DISTINCT t1.c1 TABLE1_C1,
t1.c2 TABLE1_C2,
t1.c3 TABLE1_C3,
t1.c4 TABLE1_C4,
t1.c4 TABLE1_C4,
t1.c5 TABLE1_C5,
t1.c6 TABLE1_C6,
t1.c7 TABLE1_C7,
t1.c8 TABLE1_C8,
t2.c1 TABLE2_C1,
t2.c2 TABLE2_C2,
t2.c3 TABLE2_C3,
t2.c4 TABLE2_C4,
t2.c5 TABLE2_C5,
t3.c1 TABLE3_C1,
t2.c6 TABLE2_C6,
t4.c1 TABLE4_C1,
t4.c2 TABLE4_C2,
t4.c3 TABLE4_C3
FROM table1 t1
LEFT JOIN table2 t2
ON t1.c1 = t2.c7
left JOIN table4 t4
ON t4.c4 = t2.c1
LEFT JOIN table3 t3
ON (t2.c1 = t3.c2
AND t2.c8 = t3.c3
AND t2.c9 = t3.c4)
WHERE (t2.cp5 = 0 or t2.cp5 is null)
AND (t2.c3 =
(SELECT MAX(c3)
FROM table2 s_t2
WHERE s_t2.c3 LIKE t2.c3
AND s_t2.c7 = t1.c1
) or t2.c3 is null)
采纳答案by steve
If you use distinct or group by several rows can qualify the condition. So different executions would return different row id's and the other question if several rows qualify which one would you return?
如果您使用 distinct 或 group by 几行可以限定条件。因此，不同的执行会返回不同的行 ID，另一个问题是，如果多行符合条件，您会返回哪一行？
This simply doesn't make sense
这根本没有意义
回答by Tom Warfield
Not sure what the OP was doing since there is no ROWID in the query. But this may help - just add a column alias:
不确定 OP 在做什么，因为查询中没有 ROWID。但这可能会有所帮助 - 只需添加一个列别名：
SQL>select * from (select rowid from dual union select rowid from dual);
select * from (select rowid from dual union select rowid from dual)
*
ERROR at line 1:
ORA-01446: cannot select ROWID from, or sample, a view with DISTINCT, GROUP BY, etc.
SQL>select * from (select rowid as row_id from dual union select rowid from dual);
ROW_ID
------------------
AAAAB0AABAAAAOhAAA
1 row selected.
回答by StewS2
Another possibility: Having just suffered through this error the past 3 days, I have another combination of options that might cause this.
另一种可能性：在过去 3 天刚刚经历过这个错误，我有另一种可能导致这种情况的选项组合。
- your query uses the ANSI-style joins and
- one or more of the tables involved is a materialized view or tablethat has QUERY REWRITEoption granted
- 您的查询使用 ANSI 样式的连接和
- 所涉及的一个或多个表是已授予QUERY REWRITE选项的物化视图或表
You may get the Oracle error:
您可能会收到 Oracle 错误：
ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
ORA-01445: 无法从没有保留键的表的连接视图中选择 ROWID 或采样
We finally discovered that 2 of the tables in a large query had this option set and was throwing this error. The permission had been granted to our schema for these two tables.
我们最终发现大型查询中的 2 个表设置了此选项并抛出此错误。这两个表的权限已被授予我们的架构。
It took so long to determine this was the cause because, 1) the tables are owned by another team, and 2) the tables had granted QUERY REWRITE permission to our schema, but the query (compiled in a daily job) didn't fail until 3 weeks later, after a system update.
花了很长时间才确定这是原因，因为，1) 表归另一个团队所有，2) 表已授予对我们架构的 QUERY REWRITE 权限，但查询（在日常工作中编译）没有失败直到 3 周后，系统更新后。
The team that granted this right has revoked it, but the problem persists. So our short-term work-around for the daily job is to add the following to the action:
授予此权限的团队已撤销它，但问题仍然存在。因此，我们日常工作的短期解决方法是将以下内容添加到操作中：
ALTER SESSION SET QUERY_REWRITE_ENABLED = FALSE;
Follow-up Questions we have:
我们的后续问题：
We are still pondering how to force the optimizer to re-parse the query to recognize we no longer have this grant, but it might take a system reboot.
While I recognize a materialized view produces a physical table, I'm also still asking myself why a table that's not part of an mviewwould be granted this right.
我们仍在思考如何强制优化器重新解析查询以识别我们不再拥有此授权，但这可能需要重新启动系统。
虽然我认识到物化视图会生成物理表，但我仍然在问自己为什么不属于 mview 的表会被授予此权利。
